SQL Server Reference Guide

I've covered the tools for SQL Server in previous articles. If you haven't reviewed those yet, just start here and then click the "Next" button to go to the next version. Although you’ve use these interfaces to SQL Server’s engine for most of your tutorials, you haven’t examined its little tricks yet. In this article, you’ll explore some of the lesser known uses of the User Interface (UI) tools in SQL Server. I'll break out each version into its own article, but read them all — many tips from one version carry over to another. If they don't, and you wish that they did, just go to http://connect.microsoft.com and search to see if someone has already requested it. If they did, vote to bring it back. If no one has suggested it yet, then suggest it yourself and have others vote on it. Microsoft has been known to "do what they are told" from time to time.

Before we get started, it's worth mentioning that most of the "higher" version tools can manage a "downlevel" system. In other words, you can use the 2000 tools to manage a version 7 system, the 2005 tools to manage a 2000 system and so on. The only caveats are that you can probably go only a version or two apart, and even then many of the features in one set of tools can't magically appear in the lower version. For instance, SQL Server 2008 introduced Intellisense, but that feature doesn't work against 2000 or 2005 servers. The reason is pretty simple — you can't suggest a syntax for a version you're not on!

One other question that comes up is "can a lower-level tool connect to a higher level engine?" The answer is usually yes — but once again, you're only talking one version higher, and you'll then lose some of the cool new features each tool introduces.

Let's talk moment about the connection options you have with these tools. In many shops, the server is not in the same area as the DBA. In some cases, you aren't even on the same domain. How do you connect to the server when you're in this situation?

One option is to install the tools on the server and then use Remote Desktop or Terminal Services to connect to the server and run the tools from there. This is an option I see used quite often.

Another option is to set up a Virtual Private Network (VPN) between your workstation and the server's network and connect that way.

In any case, don't ever put a SQL Server system directly on the Internet. It's just a bad idea all the way around.

I also get asked quite often, "what if my system isn't on the same domain as the server? How do I use integrated authentication and still connect in to the other domain?" The system doesn't allow me to enter different Windows credentials, so you'll either need to use the Start As right-click option in XP, or the RUN AS option in XP and Vista to set the user you want to start with.

Enterprise Manager

Enterprise Manager is a Microsoft Management Console (MMC) used to send commands to a server running the SQL Server engine. Every edition from MSDE to SQL Server Enterprise is managed using this object-oriented tool.

The reason Enterprise Manager is called object-oriented is that there are objects on the right-hand side of the MMC panels that, when clicked, activate content on the right-hand side.

For this article, you’ll start up Enterprise Manager, located in the Start menu under Programs | Microsoft SQL Server. Before you begin working with any SQL Server instance, you need to register it. In this case, you’ve already got the local server registered, but if yours isn’t and you want to follow along, just click on Tools, then Wizards... then Register Server Wizard in the main menu bar and follow the instructions on the screen.

I won’t re-cover the material that you’ve already seen in my previous tutorial on Enterprise Manager, since by now you’re all familiar with most of the basic navigation methods. Let’s explore a couple of the tips that make using this tool really handy.

Enterprise Manager Views

While the Standard view in Enterprise Manager is useful, there are others that ease finding common tasks, especially for new administrators.

One of the easiest views to use is the Taskpad view. To access it, simply right-click any database and select View then Taskpad from the menu that appears.

Figure 98

This view shows lots of information right on the first panel. It’s actually an embedded Web page, and it’s packed with hyperlinks to more information, wizards, and other tasks.

Even if you’re a seasoned veteran, the Taskpad view is a great way to manage a database. In fact, many people missed it so much in SQL Server 2005 that they made a replacement for it!

Use Groups to Arrange Your Servers

In previous versions, SQL Enterprise Manager would automatically connect to each registered server when it started. For a large group of servers connected to one console, or servers on a slow link, this could take a lot of time. Beginning with SQL Server version 7.0, the connections only take place when the server’s name is double-clicked. You can also right-click the server’s name and select Connect from the menu to create the connection.

To manage a large number of servers, it’s useful to create groups in Enterprise Manager. You get one group for free when you start the program, called the SQL Server Group. To create another, right-click the Microsoft SQL Servers item and select Create new group from the menu that appears.

Figure 99

Creating groups lets you categorize servers and unclutter the screen of a central management console.

Even if there are a small number of servers to administer, groups can be useful. In replication scenarios, I usually set up at least three groups: Publishers, Distributors, and Subscribers. This helps when I monitor the replication results on the various servers.

Run a Query

In previous tutorials, you’ve seen that you can right-click a table or view, and then select Open table and Return all rows to see the data.

Figure 100

If you stick around in this tool, however, you can get a little more control over the query. Clicking the SQL button in the toolbar shows the query used to retrieve the results. The interesting part of this trick is that you can edit the query, typing any valid SQL syntax, and then click the exclamation mark to implement the new query.

Figure 101

If you’re not sure of the field names, you can click the button that looks like two small boxes connected with a line to get a field chooser. As you click each field, the query changes to include what you want.

Figure 102

OK, so that’s really not showing off the power of this button. To see how it helps, you click the plus sign with the little box beside it, select another table, and if the primary and foreign keys exist, the join syntax is automatically displayed.

Figure 103

There are other buttons here for sorting and grouping. You may have noticed that this tool looks strangely familiar. That’s right, it looks a lot like the query builder in Microsoft Access. Using this trick helps smooth the transition to the biggest member of the Microsoft database family.

While you’re here, it’s important to know that the results grid at the bottom is editable. You can (and maybe shouldn’t) edit these cells directly. Anything you change instantly changes in the database. While this is a terrible idea most of the time, there are situations (such as in a development database) where it’s OK. For example, you might run into a time where you need a NULL value in a field. Not 0, not empty, but NULL. Typing "NULL" doesn’t work, since that just tries to enter the values N-U-L-L. To get the NULL value in a cell in this tool, just hit CTRL-0 and leave the field.

Query Analyzer

There are many other cool things you can do with Enterprise Manager, but Query Analyzer has its own nifty tricks.

As you may recall from my earlier tutorial on Query Analyzer, you establish a session with a server by calling up the tool and entering the server name and login credentials.

Create Two Windows

While you’re in the tool, you can call up another connection to a different database or even another server. To do this, select File, then Connect... from the top menu. Once you enter the server name and your credentials again, the current window is replaced with another. To see them both, select the window from the top menu and arrange them horizontally, vertically or tiled to see two sets of results at once.

Figure 104

Quickly Find SQL Errors

We all make mistakes. Finding a missed comma or misspelled word can be pretty tough in a long section of code. Those red errors in the results pane at least tell us what the error is, but it would be great to see where the error actually occurred.

Apparently, Microsoft’s programmers realize we make mistakes. Query Analyzer has a way to help you locate the exact line of code (or at least the one near it) that caused the issue: double click on the red error message. Query Analyzer will jump to the top pane, and highlight the line of code that caused the error.

Figure 105

Get T-SQL Help

If you aren’t sure of the exact T-SQL syntax in a query, you can highlight the command you’re curious about, and press the SHIFT-F1 key to go to Books Online. Books Online will try to locate and display the exact highlighted command.

Change the Results Pane Length

By default, Query Analyzer only displays 256 characters per column. To change this setting, click Tools then Options from the main menu bar. Select the Results tab to set the number of characters to a different value.

Use the Status Bar for Quick Information

Along with the Show Execution Plan, Show Server Trace, and Show Client Statistics panels (available from the Query main menu item), there’s a simple way to see row counts and time-to-execute stats. I recently saw a DBA with a stopwatch trying to time the results of a query. I pointed out that the query time is right there in the status bar at the bottom of the tool, on the right.

Undo and Block Indent

Another cool feature is that Query Analyzer has multiple undo levels. Just repeatedly press Ctrl-Z to walk back through the recent changes.

You can also highlight a block of code and press TAB to indent it all. SHIFT-TAB moves it all back.

Send a Query to a File

A serious shortcoming has been rectified in the later versions of Query Analyzer. There’s now a Results to File choice on the Query menu.

Colors Matter

It’s important to pay attention to the colors in Query Analyzer, as they can help debug code:

Keyboard shortcuts

There are quite a few shortcuts in Query Analyzer. Here’s a handy table:

Use OSQL not ISQL

SQL Server has an operating-system level command-line tool called OSQL to access the SQL Server engine. With SQL Server up and running on the local system, the format of this command is quite simple:

C:\> OSQL –E –dpubs –Q"SELECT * FROM authors"

With no server switch specified (-S) the tool defaults to the local default instance. The –E switch sets a trusted connection, and the –d sets the database. The –Q switch runs a query, returns the result to the screen, and then exits the program.

There are quite a few switches when using OSQL, from referencing a script file to setting the output, but the important thing is to realize that the switches are case sensitive. This betrays SQL Server’s UNIX roots. Run OSQL /? for a full list of the switches.

In previous versions of SQL Server, a program called ISQL was the primary command-line tool. It’s still available all the way up to SQL Server 2000, but there are two main reasons to begin phasing this tool out of batch-files and scripting.

The first is that ISQL is slated to be removed in the next version or two. Since OSQL has the same functionality, you may as well get used to ISQL not bring around.

The second reason to make the switch is that ISQL uses DB-Library, which can have problems with the use of local as a server name, among other issues. OSQL uses ODBC libraries, which have superior performance and more options.

You’ve only scratched the surface of all the cool tricks in the user interfaces of SQL Server. In future articles I'll show you how to use these tips and explore a few more.

InformIT Articles and Sample Chapters

Rick Sawtell and Richard Waymire cover working with these tools in their sample chapter: Working with SQL Server 2000 Management Tools and Utilities.

Books and eBooks

And here is the link to Teach Yourself Microsoft SQL Server 2000 in 21 Days — one of the best around on the subject.

Online Resources

Marco Bellinaso has some VB.NET code that you can use to call osql with a parameter. there are times when dimming a SQL object isn’t practical. This code does the trick!