SQL Server 2008’s T-SQL Development and Debugging Features

For many DBAs and database developers the ability to debug T-SQL without using Visual Studio (VS) is one of the best features in SQL Server 2008. SQL Server 2000’s Query Analyzer includes the ability to debug T-SQL. However, the feature wasn’t included in SQL Server 2005’s SQL Server Management Studio (SSMS) and Query Editor. Although the move to SSMS in SQL Server 2005 was well accepted, the only way to debug T-SQL scripts in SQL Server 2005 was using VS. In spite of all the talk about the crossover of developers and DBAs, this wasn’t a popular move because many DBAs don’t like to be forced to use VS. Another missing feature in SQL Server 2005 is T-SQL IntelliSense. IntelliSense was originally planned for SQL Server 2005 but dropped from the final product. SQL Server 2008’s Query Editor provides a much more integrated development experience with the addition of T-SQL IntelliSense, code outlining, and debugging.

T-SQL IntelliSense

SQL Server 2008’s IntelliSense provides T-SQL syntax checking, automatic prompting for database object names, parameter completion, and help with common T-SQL statements. IntelliSense works automatically when you begin entering T-SQL statements into Query Editor. Figure 1 shows IntelliSense’s auto object name completion feature.

You can see in Figure 1 how IntelliSense’s auto completion feature displays a list of the tables and views that are available for the HumanResources schema in the AdventureWorks2008 database. You select entries in the drop-down list by scrolling to the desired object name and pressing either Tab or Enter. One nice feature is the fact that IntelliSense will display a stored procedure’s return values and all parameters in a tool tip format when you hover your mouse over a stored procedure’s name. Incomplete T-SQL syntax is displayed using a red squiggly marker at the end of each T-SQL statement. Hovering your mouse over the words underlined in red will display a pop-up tip that describes the error condition, as Figure 2 shows.

Although very useful, IntelliSense isn’t completely full featured as it doesn’t perform T-SQL statement completion for all T-SQL statements and it doesn’t display column names for SELECT statements. That said, there are a couple of ways you can coax Query Editor’s IntelliSense into displaying column names.

One way is to first enter the clause

FROM . AS Aliasand then go back and enterSELECT Alias.which will display a list of column names. Alternatively, you can fully qualify the column names in the SELECT statement, which lets IntelliSense display the column names. For instance, if you enterSELECT ..IntelliSense will list the column names for the table.There are some places where IntelliSense doesn’t work. For example, IntelliSense isn’t active when there’s a code error above the cursor or when the cursor is in a line that’s in a comment or within a literal. It also doesn’t work for encrypted database objects. In addition, IntelliSense won’t work if the script you’re editing is larger than the default maximum size of 1MB. You can change the IntelliSense default values and turn IntelliSense on and off by navigating to SSMS’s Tools, Options, Text Editor, Transact-SQL, General, Statement completion option. You can also use the Tools, Options, Text Editor, Transact-SQL, IntelliSense tab to enable and disable IntelliSense, as well as to enable and disable the underlining of errors and outlining parameters.T-SQL Code OutliningCode outlining is a new feature in the SQL Server 2008 Query Editor that can make your code more readable. It’s especially useful for long stored procedures and T-SQL batches. The code outlining feature adds a plus sign (+) to the beginning of a block of code, which you can click to expand or collapse the code within that block. The code outlining feature works with the following T-SQL code groups:Batches—T-SQL batches are ended using the GO keyword. All the code from the start of the file to the first GO statement is considered one batch. If there’s no GO keyword, then the entire script is considered a batch. There’s one batch from each GO statement to either the next GO statement or the end of the script.Blocks—T-SQL blocks are marked using the following keywords: BEGIN - END, BEGIN TRY - END TRY, and BEGIN CATCH - END CATCH. The code outlining feature collapses the entire block.Multiline statements—Multiline statements are T-SQL statements that continue for two or more lines in the Query Editor window. The outlining feature lets you collapse these lines back to the first line.You can see an example of the new code outlining feature in Figure 3.Code blocks hidden by code outlining are indicated by gray ellipses. Positioning your mouse over the ellipsis displays the hidden code in a pop-up tool tip, as Figure 3 shows. Code outlining is also available for SQL Server Analysis Services MDX queries.T-SQL DebuggingSQL Server 2008 provides full-featured debugging capabilities that are integrated directly into Query Editor. You can start a debugging session by clicking the green arrow icon, by selecting the Debug, Start Debugging option from the Query Editor menu, or by pressing Alt+F5. (For a complete list of the Query Editor debugging keyboard shortcuts, see Table 1.) Figure 4 shows SQL Server 2008’s T-SQL debugging capability.After the debugging session has started, a yellow arrow indicates the currently executing line. In addition, the Locals window displays the value of all the variables in the current batch and a Call Stack window displays the current call stack. A Debug toolbar is also displayed in the Query Editor menu and offers the Run, Pause, Stop, Show Next Statement, Step Into, Step Over, and Step Out options. You’ll find the Debug toolbar on the far right side of the Query Editor toolbar. The Locals window, which is shown in Figure 4, displays the local T-SQL script variables and their values. You can change the variable values by clicking the value in the Value column of the Locals window and then altering the contents of the Value field. The running call stack is displayed in the Call Stack window, which is shown in Figure 4.You can use the Step Over and Step Into buttons on the debugging toolbar, or F10 and F11, to single step through your T-SQL code. The current line of code is always displayed with a yellow arrow. You can set breakpoints by clicking in the left margin of the editor. Breakpoints are indicated by a red ball icon in the left margin. If your T-SQL code is running in debug mode, it will stop when the executing script reaches a breakpoint. However, running your T-SQL code using the Execute button or F5 will run the script, but not in debug mode, and any breakpoints will be ignored and the code will run as usual.You can also inspect the values of variables by using the QuickWatch window. To display and potentially change a variable in the QuickWatch window, start your T-SQL code in debug mode, highlight the variable that you want to inspect, and select either the Debug, QuickWatch option from the menu or press Ctrl+Alt+Q to display the QuickWatch window, which is shown in Figure 5.The QuickWatch window displays all of the variables that have been added to the watch list. Variable names are listed in the Names column, contents are shown in the Values column, and the data type is listed under the Type column. To change a variable's value, simply position the cursor over the value in the Value column and alter the value that’s displayed.The Bug Stops HereSQL Server 2008’s T-SQL debugger is an invaluable addition to every DBA and developer’s toolbox. Although using the new debugger is pretty straightforward, there are a couple of caveats to making it work remotely. You can refer to the sidebar "T-SQL Debugging System Requirements" for more information about the prerequisites for using the debugger. If you spend a significant amount of time writing T-SQL scripts and stored procedures, the new development and debugging enhancements are reason enough to upgrade to SQL Server 2008.