Home arrow SQL Server arrow Page 5 - 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 5 of 10 )

Parse (Ctrl+F5)

This option will take any code entered in to the active Query pane, and will parse it for any errors. It will not actually execute the code, but will check it for syntax errors only. It will not validate database objects or ensure that variable settings are correct. This is very useful when wishing to check that your query syntax is valid before execution to remove typing errors.

Execute (F5 or Ctrl+E)

This will take the active Query pane and execute the code within it. It will not execute any other pane that is within Query Analyzer, only the pane with focus. We will be using this very exhaustively throughout the book, as this is the method for running examples placed into Query Analyzer. This will be used a great deal so there's no need to bore you with it now.

Cancel Executing Query (Alt+Break)

If you have a query that is running and you no longer wish it to continue, you can cancel, or attempt to cancel the query by pressing these commands. If you are on a locally installed SQL Server, then the cancellation should happen; however, if you are connecting to a remove server, you are sending the cancel to that server and you are then relying on the server to cancel the task in time. This may or may not happen depending on what process the server is running. Then you get into more complex areas of having to cancel your task by issuing specialized commands, which require a great deal of care.

Query | Results in Text (Ctrl+T)

The three Results in… options are properly demonstrated later in the book. However, we will quickly explain what these options are now. The first option, Results in Text, shows any results in the Results pane in a textual format. The results will be in a fixed-width type font so that characters can be lined up, and the data will be presented in a columnar fashion, tabbed so that the data from the correct column is under the correct heading. Below is a graphic demonstrating a set of data brought back from the pubs database using the Results in Text option:

Query | Results in Grid (Ctrl+D)

The previous option leaves a lot of wasted space. Therefore, you may wish to select the Results in Grid option. The one area you have to be careful with this option though, is that the messages returned from queries are placed in a separate tab. Here is the same data from the pubs database, shown in a grid format. You will notice that more data is displayed, as the grid takes the item with the longest length for that column and makes this the column width:

Query | Results to File (Ctrl+Shift+F)

This is quite simply a method for placing the results from a query directly into a file. I won't say much more about this now, as it is covered in detail later in the book. Suffice to say that the data returned from the query is not displayed on screen, but saved to a file that can be opened outside of Query Analyzer.

Query | Current Connection Properties

The connection to the SQL Server instance can be tailored to meet your specific needs. For example, it is possible to build a query and check its validity without actually running it, or to check how well it will perform when it is run.

The Current Connection Properties dialog, reached via Query | Current Connection Properties, allows you to tailor such options. At this juncture, I won't go through all the options as some are too advanced for this stage of the book. However, I will go through the options that you will use most frequently.

  • Set noexec: This will take the code entered, parse it, and ensure the code is valid. If there are any syntax errors then you will see the relevant error message returned. Ignore the minor detail that you may not know what the following command means, but enter it in to a Query pane and execute it.

    SELECT * FORM Customers

    You will then see the following error message:

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'Form'.

    It will also check any objects listed, to be sure that they are valid within the database or server. It compiles the code to achieve this.

    This option can also be set directly with SQL query commands. You would enter the following into the Query pane:

    --To turn set noexec on

    SET noexec ON

    --To turn set noexec off

    SET noexec OFF

    Using Current Connection Properties dialog is simpler if you want to alter several different properties at once, but using SQL query syntax is better if you are switching an option on and off frequently while testing code in Query Analyzer.
  • Set parseonly: Set parseonly differs from Set noexec in that it does not compile the code, and also does not check that any objects specified in the code are valid. However, it does check if the code parses correctly. For example, it will ensure that the SQL commands are spelled correctly but, if you misspell a column name in a table, it will still pass the code as valid.

    The query syntax for this command is:

    SET parseonly ON

    SET parseonly OFF
  • Set rowcount: This will set the number of rows shown in the Results pane that a query returns. This is useful if you have a very large table, but you only wish to return the first n rows.

    The syntax for this command is.

    SET rowcount n

    where n is the number of rows you want to return:
  • Set ansi_defaults: This box is grayed by default. All of the options below the Set ansi_defaults check box if checked would make the Query Analyzer comply with a standard set by ANSI, called ANSI SQL-92 compliance. ANSI is the American National Standards Institute, which discusses and ratifies standards for the industry. So, to comply with the ANSI SQL-92 standard, these check boxes have to be all checked. If you alter any, you are making your database non SQL-92 compliant. That said, there is no problem if your database is compliant or not from your own viewpoint, but if you moved to another database, then you might be used to non-compliant settings and therefore confusion may arise, or vice versa. Also, not all the commands within SQL Server meet the ANSI standard anyway, which is also the case for most databases. Each database system has its own proprietary code to complete specific functions.
  • Set quoted_identifier: I find that this is my favorite, but most troublesome option. This option sets whether two double quotes delimit the contents of a string, or whether double quotes define identifiers (for example, the name of a column within a table). SET QUOTED_IDENTIFIER OFF allows double quotes to be used to define string static values within a query. There are pros and cons for both choices, which I will demonstrate shortly.

    First of all, the syntax for this command is:

    SET quoted_identifier ON

    SET quoted_identifier OFF

    I run with this set to OFF so that I can use single and double quotes in my code to define strings. This allows me to use "O'Malley" as a valid string, because 'O'Malley' would not be valid. But, be warned! There are a couple of areas in which you cannot use this option and have to resort to single quotes for string identifiers; see below:
Try It Out – Quoted_Identifier

1. To demonstrate what I mean, enter the following code into a new Query pane.



DECLARE @surname VARCHAR(20)

SELECT @surname = "O'MALLEY"

PRINT "This will work...." + @surname




DECLARE @surname VARCHAR(20)

-- This next line will produce an error

SELECT @surname = "O'MALLEY"

PRINT 'This will NOT work....' + @surname


DECLARE @surname VARCHAR(20)

-- This next line will produce an error


'However this does work as I have a second single quote in the name....' + @surname




2. Now execute it, either by using the menu (Query | Execute), by pressing Ctrl+E or F5, or by clicking on the Execute Query button on the toolbar (the green triangle). You will see the following results:

Don't worry too much about understanding the code but, as you can see, I had to alter O'Malley to make it work when I had Quoted_Identifier set to ON. However this is all covered later in the book when inserting data in Chapter 12.{mospagebreak title=Chapter 3: Query Analyzer (contd.)&toc=1} The Tools Menu

In this there are a few useful options, and some that we will not cover in this book. Many are detailed below.

The Tools | Options Menu (Ctrl+Shift+O)

Selecting Tools | Options brings up a multi-tabbed dialog box which is used for setting up many different options within Query Analyzer. We'll look at three tabs with the most frequently used options in this section.

The General Tab

This tab is perhaps one of the more informative and useful tabs for you as a new SQL Server developer. It is concerned with where and how files are stored.

The first three options define where specific files, which have been or will be saved, are located. The default is for the local user's hard drive. However, you may find that your organization has a central folder on a network drive where it stores queries, etc., so that they are available to all. You may wish to alter these directories to point to an area in your organization like this.

Move on to the three extension boxes. These are the default extensions placed on files when they are saved. Personally, I would not alter these, as they are standard extensions.

The three check boxes at the bottom are fairly self-explanatory.

The Load and Save buttons, at the bottom on the left, allow the whole configuration tool to be saved to a configuration file, with a .sqc extension. Again, this allows one configuration file to be set up and distributed to every user, to assist with implementing standards within an organization.

The Editor Tab

While using the Query pane to edit your SQL code, there will be times when you need to alter certain aspects of the editing process.

  • Number of Undo buffers: The number of actions performed within Query Analyzer that can be moved back by pressing Ctrl+Z. Twenty is the maximum. This works in conjunction with the next two options.
  • Maximum Undo buffer size (in lines): There is a separate limit on the number of lines of code that can be stored, up to 1000. For instance, if you delete 1000 lines in a single action, you will not be able to undo the previous action even if you have 20 undo buffers. I doubt very much if you will ever breach the 1000 line limit.
  • Undo buffer limit handling: If you do reach the 1000 line limit, though, this box determines what happens in QA. The two options are to Display Message Box or Default to Discard. The latter means that the lines of data in the undo buffer will be lost. I recommend that you leave this option as it is.
  • Tab size (in spaces): The number of characters inward that are moved when a tab function is performed within the Query pane.
  • Save tabs as spaces: Select this option if you wish to convert tab characters to spaces. In this instance, a single tab would be converted to eight spaces. I tend to leave this unchecked in case other people have different tab settings and then open up a saved query.
  • Disable dragging text within editor: If you want to avoid accidentally moving code with a drag operation, select this option.
  • Default (non-Unicode) file open format: Whether you wish to open files in ANSI format or OEM format. With traditional formats for defining characters, only 1 byte of data is used, therefore you can only have up to 256 different characters. The Unicode format takes up 2 bytes of data and therefore you can have up to 65,536 characters. By why do you need so many when there are only 26 letters in the alphabet? Because using Unicode allows you to have multiple languages stored as one set of data. OEM is a different type of code page to ANSI, for example MS-DOS code page 437 uses OEM.
  • Tabbed mode (vs. Splitter mode): At present, the Results pane sits below the Query pane but it is possible, by selecting this option, to have the Query pane and Results pane separated onto separate tabs. Selecting this option opens up the last two options.
  • Switch to Results tab after query executes: Once you execute a query, you will immediately find yourself on the Results tab if you select this.
  • Show tabs on top: Quite simply, whether the Query and Results tabs are displayed at the top of the window. Personal preference.
The Results Tab

This whole tab is concerned with how results are displayed within Query Analyzer. The first option is just like the Query menu options, which declare whether the results are displayed in text or grid, or saved to a file.

The next option, Results output format, is probably best left as it is. I have found that altering this, to tab delimited, perhaps, is more troublesome than what it is worth. However, do experiment so that you know what each of the options does.{mospagebreak title=Chapter 3: Query Analyzer (contd.)&toc=1} Here is a quick summary of each of the other options:
  • Maximum characters per column: The maximum number of columns on any output. For wider columns if you want a larger output, then increase this value.
  • Print column headers: When this option is selected, each column of results has a header indicating what the information in that column relates to, and which column it relates to in the relevant table(s). Very useful.
  • Scroll results as received: As each line of results is returned to the Query pane, a scroll action occurs if the Results pane is full. With this option on, once the query has run, you will be at the end of the set of results.
  • Output query: Will print up to the first three lines of the query in the Results pane.
  • Right align numerics: Any numerics are right aligned in the Results pane, rather than the default of left aligned.
  • Discard results: When a query has finished, no results are displayed in the Results pane if this option is checked. I am not sure when you would ever use this option!
  • When a query batch completes: This will play a tone to alert you that a query has finished.
The Connections Tab

This tab is not covered here because it deals with quite advanced settings for connecting to the server. Perhaps the only option that might be of interest is Use regional settings when displaying currency, number, dates, and times. By checking this option, you are instructing QA that any output received from SQL Server will respect the locale settings on the computer that SQL Server is installed on. If the option is off, no conversion takes place.

This option only affects the display of data, and not data entry. It can cause confusion if this option is switched on and the machine on which Query Analyzer is running has a different locale setting to that which was installed with SQL Server. Consequently, I tend to keep this switched off.

The Connection Properties Tab

This is exactly like the Current Connection Properties dialog in the Query menu, as shown above, except that this manages the default settings when you log in to SQL Server each time.

The Fonts Tab

This deals with the fonts you wish to use in Query Analyzer.

The Script Tab

These are options that you can set when outputting any objects as a script. For example, you can output the SQL required to build the Northwind database to a SQL script file. These options are all self- explanatory. I recommend leaving them as they are.

The Tools | Customize Menu

The options in this menu enable you to associate particular functions or key combinations with specific combinations of keys. This cuts down the amount of time required to perform actions in Query Analyzer, and reduces the potential for typos when running frequent commands. Similar simple macros exist in other products like Word and Excel.

When executing sequences of commands stored in this way, it is impossible to combine them with the input of extra values. Consequently, this functionality is different to that of templates, which allow you to input values for specified options before execution.

The next Try It Out section demonstrates how to customize keystrokes and then use them in Query Analyzer. Two different keystroke commands will be created – one to change the default database for a specific user, and one to go back to the Northwind demonstration database for the same user. This is NOT altering the database that you are working with, just the database that will be connected to when that specific user logs in to Query Analyzer.

So far there are only two main User IDs in our SQL Server. One is the sa login, which was covered in Chapter 1 and which is added to SQL Server by default. The second ID is the Administrator login ID, in the case of the book, RDewson, which was used to install SQL Server and which is the login ID on my machine, and has full administration rights. There will be new IDs later but, for the moment, I will still be working with the Administrator login.

Try It Out – Customizing Function Keys

1. Ensure that Query Analyzer is running. In this case, I have logged in as Administrator. It is important to know as whom you have logged in, so that you can create the correct information in the key assignments.

2. From the menu, select Tools | Customize:

3. This brings up the Customize dialog, which can be used to customize key combinations. Notice that there are a few key combinations populated already:

4. Edit one of the empty assignments. I will choose Ctrl 3 for the following code:

sp_defaultdb "WONDERBISON\RDewson","pubs"

Obviously, alter the WONDERBISON\RDewson login to one that is valid for your machine. You should end up with something like this:

5. Enter the following code against another empty key assignment:

sp_defaultdb "WONDERBISON\RDewson","Northwind"

This time I chose Ctrl 4:

6. Now click OK, which will apply the changes and remove the dialog.

7. Now move to Enterprise Manager and expand the server nodes until you get to the Security node on the installation of SQL Server. Expand this node and click on Logins:

8. On the right-hand side, the Details pane, find the ID that you have set up to alter the default database with. In my case, it is the RDewson ID. Notice that, in this row, the Default Database is set to master:

9. You can also find this same information within the SQL Server Login Properties dialog; double-click on the ID to display it. Notice that the default database at the bottom of this screen is currently set to master:

10. Click Cancel, and switch back to Query Analyzer.

11. Now press Ctrl 3. You should see a Default database changed message in the Results pane.

12. Switch back to Enterprise Manager and click the Refresh button. If this button is not visible, ensure that there are no login IDs selected on the right side.

13. Notice that the Default Database column for the RDewson ID is now set to pubs, just as we expected:

14. Now hit Ctrl 4 to switch back to Northwind.
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