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