Creating T-SQL Statements in SQL Server 2005 and Visual Studio 2005

In SQL Server releases before SQL Server 2005, Microsoft provided several ways to build T-SQL statements. However, many interfaces have changed in SQL Server 2005. Can you provide an overview of some ways I can create and test T-SQL statements?

SQL Server Management Studio (SSMS) is the primary tool in SQL Server 2005 for building and testing T-SQL statements. SSMS replaces Enterprise Manager and Query Analyzer and provides one interface for managing SQL Server—including the database engine, Analysis Services, SQL Server Integration Services (SSIS), and Reporting Services—and building, editing, and testing T-SQL, Multidimensional Expressions (MDX), Data Mining Extensions (DMX), XML for Analysis (XMLA), and SQL Server Everywhere (aka SQL Server Mobile) queries. Many of these features are designed for all levels of users, from the experienced T-SQL developer to the DBA who creates the occasional T-SQL query.

To build a query in SSMS, start the tool from the Microsoft SQL Server 2005 program group. Once SSMS is running, open a query window to create and test the statement. The type of query window you open depends on the type of statement you plan to build. For T-SQL statements that access data from a relational database, click Database Engine Query on the toolbar, then connect to the applicable instance of SQL Server. If you already have an active databaseengine connection (as opposed to another type of connection, such as a connection to an instance of Analysis Services), you can click New Query to open the query window.

When the query window appears, you'll see one pane in which you type your T-SQL statements. That pane is Query Editor, which supports colorcoded keywords, line numbering, code parsing, and numerous other features. When you run a query, a second pane appears and displays the results of your statement execution. Figure 1 shows a database-engine query window with the Query Editor in the top pane and the results in the bottom pane.

Now, let's look at an example that illustrates how to build T-SQL statements in SSMS. Suppose that you need to access data from the Inventory database, which stores book and author information for a bookstore. The database includes the Books, Authors, and BookAuthors tables, which are defined in the code at callout-A in Listing 1. As the table definitions in the last few lines of callout A show, the BookAuthors table contains foreign keys that reference the Books and Authors tables. (For an explanation of what a table definition is, see the "Table Basics" topic in SQL Server 2005 Books Online—BOL. For an explanation of what a foreign key is, see "Constraints" in the Data Integrity Basics section of BOL.) BookAuthors acts as a junction table to support the many-to-many (M:N) relationship between the Books and Authors tables. (One or more authors can write one or more books.) The code at callout B shows the INSERT statements that add sample data to the three tables. As you might notice, Annie Proulx (whose author ID, 105, you can see in the first section of INSERT statements) is the author of two books, and Juliet Sharman-Burke (author ID 102) and Liz Greene (author ID 103) are the co-authors of one book (The Mythic Tarot, book ID 3214, as you can see in the last section of INSERT statements in callout B). The diagram in Figure 2 shows the three tables populated with values. Notice that Annie Proulx is joined to her two titles through the BookAuthors table.

These examples demonstrate the M:N relationships between the Books and Authors tables. To test this information for yourself, access the electronic version of this article at http://www.windowsitpro.com/windows scripting, InstantDoc ID 50502, then copy Listing 1 to the SSMS query window. Alternatively, you can download the code associated with this article by accessing the .zip file, as the "On the Web" box on page 1 explains. Open the REM_0608_SQL_scripts .sql file in SSMS, then highlight Listing 1's script for creating and populating the database and click Execute. Be sure that you run this script only against a test system and that a database named Inventory doesn't already exist.

Now, suppose that you want to retrieve a list of titles and authors for books that have existing stock of more than 5 (i.e., books that have a bkInStock value greater than 5). You can use Query Editor to create a TSQL statement similar to the one that Listing 2 shows, in which the query uses inner joins to join the three tables, based on bkID and auID values. The WHERE clause limits the result set to rows that have a bkIn-Stock value greater than 5, and the ORDER BY clause sorts the rows in ascending order, first by book title, then by last name, and finally by first name. The results pane in Figure 1 shows the results that the SELECT statement returns. Assuming you created the Inventory database as described in the previous paragraph, you can copy Listing 2 to a query window in SSMS to verify the results.

As you can see, the SSMS Query Editor (like Query Analyzer in previous SQL Server releases) provides a straightforward method for creating, editing, and testing T-SQL statements. However, T-SQL statements aren't always as simple as the one in this example. Joins and other conditions can become quite complex, and it would be nice to have a little help in building more complex queries. That's where Query Designer comes in.

Query Designer is a tool in SSMS that provides a graphical UI for building T-SQL statements. You access the tool from within the query window where you want to build your query. For example, to create Listing 2's SELECT statement, take the following steps:

Once you use Query Designer to create your basic table, you can modify the statement as necessary to add logic. For example, you might decide to concatenate the auFirstName and auLastName values so that author names are listed in a single column. You can also check the statement syntax before actually executing the statement. Simply click Parse on the toolbar.

As you can see, SSMS provides a handy way to create, edit, and test TSQL queries. In addition to Query Editor and Query Designer, SSMS includes Template Explorer, which contains an extensive set of templates that you can use to create various types of T-SQL statements. However, you might not always want to build and execute your statements in SSMS. For example, you can use the sqlcmd command-line utility to interface with SQL Server databases. The sqlcmd utility replaces both the isql and osql utilities. (SQL Server 2005 doesn't support the isql utility, and the osql utility doesn't support all SQL Server 2005 features.)

You can use the sqlcmd utility to execute any T-SQL query, such as the statement in Listing 3. However, executing the statement at the command prompt can be a bit cumbersome, especially if the statement hasn't been tested. For example, the command in Listing 3 includes the sample SELECT statement and the parameters necessary to connect to the local server and send the output to the C:inventory.txt file. Using this method to execute your statements can be time-consuming and frustrating, especially if you encounter errors and have to modify the command and run it multiple times.

Of course, you can use SSMS to test your T-SQL statement, then paste it into the command when you enter the command at the command prompt. But a better method is to create a script file that you simply call with the sqlcmd utility. The great part about this technique is that SQL Server lets you create a script file that contains both the necessary T-SQL commands and the sqlcmd commands.

For example, if you refer to Listing 4, you'll see an example script file (CmdSpecs.sql) that contains the SELECT statement you saw earlier. The only difference in Listing 4's code is that the SELECT statement includes the database name when it identifies the tables. (You can also pass the database name as a parameter at the command line.) Notice also that the SELECT statement is preceded and followed by a sqlcmd command (these are the lines that begin with colons). When you include sqlcmd commands in a script file, you must precede each one with a colon. In addition, when editing the script file in SSMS, you must activate script editing in the query window by clicking SQLCMD Mode on the toolbar. After you've created your SQLCMD script, you can click Execute on the SSMS toolbar to test the script.

The best part about creating a script file in SSMS is that you can take advantage of many of the features of Query Editor, such as colorcoded keywords, script parsing, and Showplan. You can even use Query Builder to create your T-SQL statement, then add that statement to the script file.

Once you've created and debugged the T-SQL and sqlcmd commands, you should save the file, then use sqlcmd to call that file from the command prompt. For example, the following command uses the -i switch to call the C:CmdSpecs.sql file, which contains the necessary script:

sqlcmd -i c:CmdSpecs.sql 

Using this technique is less cumbersome than trying to create your entire statement at the command prompt. However, this example and the other examples in this article by no means provide full explanations of the features available to the sqlcmd utility, Query Editor, Query Builder, or SSMS. For more information about each of these tools, see SQL Server 2005 BOL.

Of course, you might do all your development in Visual Studio, in which case you probably won't want to build and test your T-SQL statements in SSMS or in a sqlcmd command. Fortunately, Visual Studio 2005 Professional Edition and higher includes the Server Explorer interface, from which you can add a database connection, then select New Query to build a query. To create the SELECT statement in Listing 2, take the following steps:

Like SSMS, Visual Studio 2005 lets you perform many database operations. For example, you can use Visual Studio templates to create a variety of scripts—such as table, trigger, and stored-procedure scripts—and you can use Query Designer to build INSERT, UPDATE, and DELETE statements. Visual Studio 2005 also provides an interface for using the Common Language Runtime (CLR) to program databases. Even if your primary function is programming and you use the Visual Studio 2005 IDE, we recommend you install SSMS because it's dedicated to managing SQL Server databases and related services.