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.

I covered the SQL Server 2000 tips in the article just before this one, but I'll repeat some of what I said there so in case you're only interested in this version you don't have to switch back and forth. If you did read the previous article, then just treat this one as a refresher!

Although you’ve use these interfaces to SQL Server’s engine for most of my tutorials, we 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 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.

SQL Server Management Studio

SQL Server Management Studio is a new tool for SQL Server 2005 that is based on Visual Studio, although it was re-worked for the DBA. For instance, Visual Studio usually starts with a Project, and SSMS won't make you do that — although you can make a Project or Solution if you want to.

Like the older Enterprise Manager (EM), SSMS is called object-oriented because there are objects in the Object Explorer that, when clicked, activate content on the right-hand side.

SSMS works differently than EM, because you can either connect to a server right from the start, register a server, or click "Cancel" and work disconnected if you wish.

You can still register a server in SSMS, but you'll need to show the panel it's on to do it. That's my first tip — panel arrangement.

Arrange your panels

If you don't have the panels arranged in any particular way in SSMS, try this: on the menu bar, click Window and then Reset Window Layout. That will put all of the panels the way they shipped with the product. Now you can click View and then Registered Servers. Depending on your system, the Registered Servers panel may come up in a tabbed window on the left-hand side of your screen or as a "docked" window, meaning one that is stacked on top of another. Either way, you can click on the small dpownward facing arrow icon in that panel and see a list of display options. You have the ability to "float" the window, meaning it can be moved anywhere on the screen, have the panel "Auto-hide," meaning you can rest your mouse over it to show and when you're done it will slide out of sight, or you can "dock" the panel. That last one is the option I choose most often. If you choose to dock the window, you can "grab" its main title bar and start to drag it. You'll notice small icons showing positions all over the screen. Just place the window on one of those icons and you'll be able to arrange your environment any way you like.

I won’t re-cover the material that you’ve already seen in my previous tutorial on SSMS, 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.

Options

If you've used Enterprise Manager and Query Analyzer in the past, chances are you might not necessarily like SSMS. That's understandable, since most people hate change. But change can be a good thing. But if you still want some of the same behaviors you had in SQL Server 2000, such as the keyboard shortcuts, you can set them that way using the options. To get there, click Tools and then Options in the menu bar.

The best thing to do is just walk up and down the tree, examining the amazing amount of options you have there. You can set everything from how the results are displayed after a query to the screen colors and layouts. I want to call out a couple of useful options that I get asked about all the time.

Query Window by Default

Many people really miss the ability to connect to a server and be in a query window. Sure, you just have to click the New Query button, but sometimes you want to jump right into a query. And with SSMS, you can.

There are actually two ways to do this. The first is to drop to a command prompt in Windows and type SQLWB.EXE name of script file. Replace that last bit with the name of a .SQL file and you'll jump right into that file. I make a blank SQL file on my system and then create a shortcut to do this.

But you can also have a blank environment by default. Just click Tools and then Options in the menu bar, and then right there on the first screen you'll see an option called At Startup. You can ask for a blank query window by default there.

Script with All Options

In SQL Server you could ask for a script and a set of options was displayed. In SSMS, you have a lot more options for where to send a script, but the options aren't there, and some things, such as permissions, aren't on by default.

Once again, check the Tools and then Options item in the Menu Bar. There towards the bottom is the Scripting item, and you'll find even more options than you had in SQL Server 2000 available.

Use the Reports

You no longer have the TaskPad view from SQL Server 2000, so people sometimes wonder where they can find a general system information view. Well, you now have 50 reports that show far more information than the TaskPad ever showed. Just right click the Server object in the Object Explorer and look for the Standard Reports menu item. Databases and most other objects also have Standard Reports.

You're also able to create your own Custom Reports for SSMS objects — but that's another article.

Oh, you'll only have these options on the right-click if you have Service Pack 2 installed on your client system. If you don't have these options, this is a great time to stop what you're doing, connect to the Internet, and get that installed right now. Before you do anything else.

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.

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

Run a Query

Just like in EM, you can right-click a table or view, and then select Open table to see and even edit the data.

Also like EM, 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.

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.

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. There are other buttons here for sorting and grouping.

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.

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. the Query Window 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, and you jump to the top pane, and highlight the line of code that caused the error.

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.

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 SSMS 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.

Colors Matter

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

You do have some control over these colors in the Options area, so these might have changed on your system.

Use SQLCMD not OSQL

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.

OSQL uses ODBC libraries, which have superior performance and more options.

Even better is the new SQLCMD interface. You have all of the same options available, and a few more, and it uses the faster and more secure Native Client library to access the server. Once again, type SQLCMD /? for a list.

We’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

If you're using SQL Server 2005 Compact Edition, you have some special caveats around the tools. Prashant Dhingra and Trent Swanson have some tips for you in their sample chapter called Getting to Know the SQL Server 2005 Compact Edition Tools.

Books and eBooks

I'd be remiss not to mention my own book on these great tools. You can find it here.

Online Resources

Microsoft has a great set of tutorials on SSMS here.