Home arrow SQL Server arrow Page 4 - Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Wrox are well known for their "How-To" series of books, which teach beginning-intermediate developers how to use Microsoft technologies properly and efficiently. Today, Mitchell takes a look at chapter three from "Beginning SQL Server 2000 Programming", which is published by Wrox Press.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 23
January 04, 2002
  1. · Sample Chapter: Wrox Beginning SQL Server 2000 Programming
  2. · Chapter 3: Query Analyzer (contd.)
  3. · Chapter 3: Query Analyzer (contd.)
  4. · Chapter 3: Query Analyzer (contd.)
  5. · Chapter 3: Query Analyzer (contd.)
  6. · Chapter 3: Query Analyzer (contd.)
  7. · Chapter 3: Query Analyzer (contd.)
  8. · Chapter 3: Query Analyzer (contd.)
  9. · Chapter 3: Query Analyzer (contd.)
  10. · Summary

print this article

Sample Chapter: Wrox Beginning SQL Server 2000 Programming - Chapter 3: Query Analyzer (contd.)
(Page 4 of 10 )

You can directly relate what you see in this dialog to necessary information within the template. This makes the whole process of building a useful query to perform potentially complex functions a great deal easier. Click Close to remove the dialog for the time being.

The Query Pane

The Query pane sits on the right of the Object Browser and, as you can see in the screenshot below, I have placed some text within it to denote where it is. When you enter SQL code to run against your SQL Server instance, database, tables, or any other valid query statement, you enter the code in the Query pane:

Note that, when you close a pane, if that is the last pane that is connected to a specific server, the connection to that server will be ended.

The Results Pane

The Results pane is used to display any results and messages from running SQL code placed in the Query pane. It is always found below the Query pane. I have added text to show its location in the screenshot above. The Results pane doesn't display by default. You need to switch it on by actually running a query, by the toolbar button, or through the menu: Window | Show Results Pane. Ctrl+R toggles the Results pane on and off.

Menu Options

Like every Windows-based product, Query Analyzer comes with a menu containing the necessary functionality to make it work. In this next section, the menu items will be described so that you know what is available to you, and why. Some menu choices are standard to Windows products, for example, File | Exit, and we will not cover them here.

The File Menu

The menu will be discussed from left to right. Let's start with the File menu.

File | Connect (Ctrl+O)

Use this if you have disconnected from a server and wish to either reconnect or connect to a different server entirely. You can also add a server, which is not listed, to the Object Browser. When you select this item, you will be presented with the Connect to SQL Server dialog that you would see if starting Query Analyzer from the Start menu.

Query Analyzer allows you to connect to different servers at the same time with each connection having its own Query and Result panes.

File | Disconnect (Ctrl+F4)

Use this if you wish to disconnect and lose the connections that you have with the server that has the connection in the current Query pane. As I said previously, more than one server can be connected to at any one time. By selecting a Query pane that is connected to the server, and disconnecting by selecting this menu item, all connections to that server will be lost. The Query pane with the connection will be closed, and you will be prompted to save any code that you have entered or changed.

File | Disconnect All

As you might expect, this option is very much like Disconnect, except every connection to every server is lost. All Query panes are closed and, again, you will be prompted to save any code that you have entered or changed.

File | New (Ctrl+N)

File | New through the menu allows the creation of a new blank query pane, or a template for one of several functions. Don't worry about templates for the moment they are covered later in the chapter. When you select File | New, a pop-up dialog appears, allowing you to select an item to create in a new query pane. Here, I've selected Blank Query Window:

Using File | New through the menu works differently to pressing Ctrl+N, even although the two are linked on the menu. Hitting Ctrl+N simply creates a new blank Query pane and does not bring up the dialog.

File | Open (Ctrl+Shift+P)

Open opens a selected file in the active Query or Results pane, overwriting anything already entered there. Of course, if you have any unsaved items, you will be prompted to save these first.

File | Save (Ctrl+S)

This will save the query text from the Query pane, or the results from the Results pane, to a file on your disk. Only the query text or the results produced will be saved, depending on which pane the active cursor is in at that time. If you want to save the query and the results, then you have to do this in two separate actions. Just like any other Save command, if this is the first time that this item has been saved, you will be prompted with a Save Query or Save Results dialog box, looking not dissimilar to this:

As you can see, I have already saved a file called Untitled1.sql. It is sensible to save your queries with meaningful names.

Once a query has been saved, Ctrl+S or the Save menu option overwrites the original file.

File | Save All Queries

Save and Save As only work with the current Query or Results pane. Save All Queries will save the details in every Query pane. Nothing from any Results panes will be saved with this option.

File | Print (Ctrl+P)

Print can only be used against a single Query pane. It cannot be used against a result set if it is displayed as a grid or multiple Query panes.

File | Exit (Alt+F4)

Exit will disconnect the connection to any SQL Server instance that you are connected to, as you can be connected to multiple SQL Server instances at once, and close down Query Analyzer.

The Edit Menu

Now we look at the Edit menu.

Edit | Clear Window (Ctrl+Shift+Delete)

This command will remove all code from the active Query pane only.

Edit | Bookmarks (Shift+F2)

A bookmark is a temporary marker, placed within a Query pane, used to mark specific areas of code that you wish to return to or highlight. This is usually used when you have more than one piece of code within a Query pane that you wish to run, or perhaps a large stored procedure that you wish to mark and then move through using the bookmarks. A bookmark is denoted by a light blue rectangular shape placed in the gray column to the left of the code:

There are the following commands, which allow you to navigate around and use these bookmarks.
  • Toggle Bookmark Ctrl+F2 - This will toggle a line of code between being bookmarked and not being bookmarked.
  • Next Bookmark F2 - This will move you to the next bookmark down from the point you are at. This will loop around to the first bookmark if it reaches the end of the code.
  • Previous Bookmark Shift+F2 This will move you to the previous bookmark upwards from the point you are at. This loops around back to the last bookmark if the search reaches the top.
  • Clear All Bookmarks Ctrl+Shift+F2 Removes all bookmarks from the Query pane.
Edit | Insert Template (Ctrl+Shift+Insert)

Earlier on, the definition of a template was mentioned. Templates are quite simply files holding templated code; saved on a hard disk that Query Analyzer knows the path to. To use a template, which is demonstrated later in the book in Chapter 14, when we look at updating rows of information within a table, you can use this command to insert the template into the active Query pane, with the contents of the template inserted where the cursor is currently positioned.

By selecting this option, a dialog box is displayed requesting you to select which template you wish to include from a selection of template groups. By navigating to the correct folder, it is possible to select a specific template.{mospagebreak title=Chapter 3: Query Analyzer (contd.)&toc=1}

Edit | Replace Template Parameters (Ctrl-Shift+M)

This was discussed in the Templates section previously.

Edit | Advanced

The Advanced menu option leads to a set of sub-menu options, which are covered here.

  • Edit | Advanced | Make Selection Lower Case (Ctrl+Shift+L): By highlighting a section of text and then using this option, all of the selected text becomes lower case. This option has limited use unless you have chosen a language installation that has mandatory case sensitivity.
  • Edit | Advanced | Make Selection Upper Case (Ctrl+Shift+U): Making all SQL commands and functions upper case helps to make your code more readable.
  • Edit | Advanced | Increase Indent (Tab): You should indent your code at appropriate places to make it more readable, as well. As the book progresses and the demonstration code becomes more and more complex, you will see these indentations in action.

    This option will only work when more than one line of code is selected. If only one line, or part of one line is selected, then that selection of code is lost and the cursor is moved across by one tab
  • Edit | Advanced | Decrease Indent (Shift+Tab): As you would expect, this is the opposite of Increase Indent.
  • Edit | Advanced | Comment Out (Ctrl+Shift+C): While you are testing, you may have several different pieces of code in your query pane, or perhaps you wish to remove a chunk of code but reinstate it later. This function enables you to comment out many lines of code at once. It places the SQL line comment symbol, two hyphens (--), at the start of each line of code, which stops those lines from executing. However, you can also comment a block of code out by using /* at the start of where you want to comment, and */ at the end. For example:

    /* This is the start of some code here

    And on to the next line

    And it ends here with the next line code to run */

    SELECT * FROM Players
  • Edit | Advanced | Remove Comments (Ctrl+Shift+R): Once you wish to remove a set of line comments, then highlight the desired lines of code and use this action.
The Query Menu

The basis of a query is a set of commands and options used to create a way of inspecting or manipulating data within a database. The Query menu is where perhaps the most useful options within Query Analyzer are found. Most of these options will be used very early on in your career as a SQL Server developer, so this section is very important.

A few of the options in this menu are very complex and are discussed in Robert Vieira's "Professional SQL Server 2000 Programming" (Wrox Press, ISBN 1-861004-48-6).

Query | Change Database (Ctrl+U)

The first option gives the ability to change which database is being used within your query. At present, you will find that your login ID is currently pointing to the master database. Recall that you really ought not to alter this database. So, how do we change this selection?

Try It Out Change Database

1. Ensure that Query Analyzer is running and that you are successfully logged in. In this demonstration, I am logged in as the Administrator, which has a default database of master. You can check this by looking at the combo box on the right side near the top of the screen.

2. From the Query menu, select Change Database. You can also press Ctrl+U.

3. This then brings up a dialog, which allows a different database to be selected. Notice that you also get two other useful pieces of information: Compatibility Level and Status.
  • Compatibility Level: The Compatibility Level displays the version of SQL Server that the database listed is backwardly compatible with. A value of 80 actually relates to SQL Server 2000, the current version, so the databases in the screenshot are not compatible with SQL Server 7.0, for instance. Values of 70 or 65 relate to version 7.0 and 6.5, respectively. Of course we're really using SQL Server 8, but in keeping with Microsoft's current naming strategy, it is called SQL Server 2000. Compatibility levels refer to many areas of the database, from functionality, to what is valid and what is not. Within this book, all the databases will be compatible with SQL Server 2000. However, if you are working with a SQL Server upgrade, you may see a database like Northwind set to a compatibility level other than 80.
  • Status: The second column is Status. You will find, as you work through the book, that a database can have several different statuses online or offline, for example. This column shows exactly what status the database has. A status of online, or blank, means the database is up, running, and ready for updates, interrogations etc. But if status is offline the database will not accept any changes, as it has been shut down.

4. Select any one of the other databases. Don't worry we won't be doing anything with it. This is just to demonstrate altering which database you are potentially going to use. In my case, I have chosen Northwind. Notice, though, that the current database is shown in light blue. Now click OK.

5. Notice that the combo box on the right, which shows the current database, has now altered to Northwind. This also demonstrates that you are now pointing at the new database.

Changing database like this alters the database that Query Analyzer is pointing to for the active Query pane only. If you have more than one Query pane, the non-active ones will remain pointing to the database that they were originally connected to.

By altering the database displayed in the database selection dialog, you need to remember that this is not a permanent change and, therefore, when you restart Query Analyzer, you will be back with your default database. We will cover changing the default database later in this chapter.

Time to move on to some of the other options under the Query menu.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials