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

Downloads
50502.zip

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:

1. In SSMS, click Database Engine Query or, if a connection to a SQL Server database is active in Object Explorer, click New Query to open the query window.
2. Select the Inventory database from the Available Databases dropdown list on the toolbar. (Be sure you used the code in Listing 1 to create the Inventory database.)
3. Click Design Query in Editor on the toolbar. The Query Designer window appears with the Add Table dialog box active.
4. In the Add Table dialog box, press CTRL, select the three tables in the Inventory database, click Add, then click Close. The top pane of Query Designer displays the three tables and their relationships. As Figure 3 shows, Query Designer has three panes. The top pane is the Diagram pane. The middle pane (the Criteria pane) lets you define the specifics of how the TSQL statement will be constructed. The SQL pane displays the T-SQL statement as it is being built.
5. In the Diagram pane, select the following checkboxes (database columns) in the following order: bkTitle, auLastName, auFirstName, and bkInStock. The order in which you select the columns is the order in which they appear in the SELECT list, which is why you want to select them in the order specified here. As you select the columns in the Diagram pane, Query Designer adds them to the Criteria pane and modifies the SELECT statement in the SQL pane.
6. In the Criteria pane, configure each column as Figure 3 shows. Notice that you must uncheck the Output column for the bkInStock row because you don't want to include the bkInStock value in the result set; you want to use that value to filter out rows that don't have a value greater than 5. When you uncheck the Output column, the check mark is also removed from the related checkbox in the Diagram pane, but the column is still included in the query. Also notice that the SELECT statement in the SQL pane is automatically modified each time you make a change to the Criteria pane. Note that you must move the cursor out of a cell in the Criteria pane to update the information in the SQL pane.
7. Once you've added all the necessary settings to the Criteria pane, click OK to close Query Designer. The SELECT statement is now displayed in the query window. You can test the statement by clicking Execute and comparing the results to the results in Figure 1.

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:

1. Open Visual Studio 2005.
2. Select Server Explorer from the View menu to open the Server Explorer pane.
3. In Server Explorer, right-click the Data Connections node, then click Add Connection. The Add Connection dialog box appears, as Figure 4 shows. We've added configuration settings to Figure 4 for creating a connection to the Inventory database on the local machine. (The connection is automatically saved in Server Explorer unless you explicitly delete it.) Notice that the Server name text box shows the name localhost. You can specify a NetBIOS computer name, an IP address, or a host name to connect to the local computer or a remote computer running SQL Server 2005.
4. From Server Explorer, right-click the Inventory database node, then click New Query to begin the Query Build process. The node directly below the Data Connections node should be <computer name>. Inventory.dbo where <computer name> is the name of the computer running the Inventory database. The Query Designer window appears with the Add Table dialog box active. At this point, you're at Step 3 in the Query Designer query-building procedure we walked through earlier.
5. Follow Steps 3 through 6 in the query-building procedure to build a SELECT statement from Visual Studio 2005. Once you've built the SELECT statement, you can run it against the Inventory database to return results. To run the SQL statement, click Execute SQL on the toolbar (the button looks like a red exclamation mark—!).

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.