Using the SQL Query Tools

As you and I travel though this and subsequent chapters, you'll notice that I illustrate many of my points using T-SQL code samplesI've already shown you a few of these. Since you'll probably want to try these out yourself, it's time to show you how these samples are edited and tested using Visual Studio and SQL Server Management Studio. If you've worked with SQL Server 2000 (or earlier versions), you're accustomed to using the Query Analyzer (the GUI query editor and tester) or the command-line tools such as ISQL or OSQL to generate, edit, and execute your T-SQL data description language (DDL)^[13] queries. You might also have used Visual Studio's "Server Explorer" to perform these tasks. If this is the case, you're well ahead of the game.

^[13] DDL is the language used to define databases, tables, and all of the other database objects.

In SQL Server 2005, all (or most) of these familiar tools have been rolled into a single application: SQL Server Management Studiothis is the tool we'll be using to illustrate how SQL Server manages the DDL and Data Manipulation Language (DML) tasks needed to create and manage databases and the objects they contain.

Creating a New Query

It's really pretty simple to create a new T-SQL query. Just follow these steps:

1.	Start SQL Server Management Studio. It's in Start | All Programs | SQL Server 2005.
2.	Open a connection to your SQL Server instance using the connection for the selected server. The first time you do this, you'll be asked to register the target server instance.
3.	Click on New Query. This opens a query editor window. You can also drill into Tables, Views, Stored Procedures or other objects; right-click; and choose to have Management Studio generate a template to modify an existing object or create a new object.
4.	Enter or modify the T-SQL. Don't worry about case unless your SQL Server instance is installed as "case-sensitive".
5.	Choose the database against which the query is to execute from the dropdown.
6.	Click on the "check mark" to test the syntax or on "Execute" to run the query. Note that if you select one or more lines before clicking on the test syntax check mark or Execute buttons, only the selected TSQL is tested or executed.
7.	View the results in the lower "Results" tabbed window and any messages returned in the "Messages" tab.

Figure 2.27 shows you what you should see at this point. I've created a T-SQL query and highlighted the areas you're likely to use most often. Notice that I executed the query, so the results window shows the rows returned.

Figure 2.27. SQL Server Management Studio's TSQL query editor.

Keep in mind that SQL Server Management Studio does not show a "live" view of the objects in the Object Explorer. That is, the list itself won't show new tables, columns, stored procedures, or anything else after they are created. You'll find yourself right-clicking the object header (like "Tables") and selecting "Refresh" to get SQL Server Management Studio to requery the system catalogs for an updated list. This also applies to data queries. The tool lets you open a window to inspect the contents of the database and even edit the data. Simply choose a table, right-click, and choose "Modify". This runs a SELECT * query against the table and displays the rowset in a new window. If the data changes, this window will not show those changesunless you're making them interactively in the window. To get a fresh copy of the data, click the "Execute" button.

I discuss how to use other features of SQL Server Management Studio to view and tune the query plan later in this chapter.