QL Server 2005 or “Yukon” is going to be a major SQL Server update containing updates to nearly every facet of the program, including T-SQL.

Microsoft has introduced a number of new features and enhancements in the T-SQL language in SQL Server 2005. Some of the new features include the PIVOT and UNPIVOT commands, CTE, DDL (data definition language) triggers, error and exception handling with the TRY/CATCH block, and ranking functions. The SQL Server team has added enhancements to the TOP clause and the data manipulation language (DML) commands (INSERT, UPDATE, and DELETE) have been enhanced with the addition of the new OUTPUT clause. Let me first introduce some of the new enhancements and then I’ll cover the new features.

The TOP Clause
The TOP clause is not new to SQL Server, but the fact that you can specify an expression as the number definition is new in SQL Server 2005 (see Listing 1). The WITH TIES option may cause more than the specified number of records to be displayed because it displays all of the records matching the last record’s ORDER BY expression (see Listing 2). You will notice that there are more than the eight records specified in the TOP() option.

You can also use the TOP clause with INSERT, UPDATE, and DELETE to limit the number of records acted upon during the data manipulation operation (see Listing 3).

Working with Data Manipulation Commands (DML) Commands and OUTPUT
In SQL Server 2000, when you wanted to determine the changes from an INSERT, UPDATE, or DELETE, your query had to make a round trip to the server, but that won’t be required anymore. SQL Server 2005 now supports an OUTPUT clause so a single trip to the server performs the data update and returns the results. Fortunately for you, if you are already familiar with the INSERTED and DELETED virtual tables from writing triggers you are more than half way home. You can use OUTPUT with an INTO expression to fill a table, typically a table variable.

In Listing 4, a CREATE TABLE statement creates the OutputTest table and then a series of INSERT commands populate it with sample data. Next, a DECLARE command creates the @DeletedTable table variable that will be populated as a result of the OUTPUT clause associated with the DELETE OutputTest command. The OUTPUT clause uses the Deleted virtual table to populate the fields in the @DeletedTable variable.

In Listing 5, a DECLARE statement creates the @UpdatedTable variable that will be populated as a result of the OUTPUT clause associated with the UPDATE OutputTable command. While the example in Listing 4 only took advantage of the Deleted virtual table, this example uses both the Inserted and Deleted virtual tables to access the before and after values of the update.

PIVOT/UNPIVOT
I’ll begin my review of new T-SQL features and commands with the PIVOT and UNPIVOT operators. The PIVOT operator provides the ability to quickly and easily generate cross tab queries. A cross tab query rotates rows data into columns data.

In Listing 6, a CREATE TABLE statement creates the MonthlyPurchaseOrders table which will be populated with a subset of the Purchase Order header information from the AdventureWorks sample database. Next, the code uses PIVOT to create a cross tab query that lists each VendorID and their cumulative order subtotal across by month.

In Listing 7, a CREATE TABLE statement creates the MonthlyPOPivot table which is populated with data from the MonthlyPurchaseOrders table PIVOTed by VendorID.

In Listing 8, a SELECT statement uses the UNPIVOT operator on the data in the MonthlyPOPivot table to rotate from column-based data to row-based data.

Exception Handling with TRY/CATCH
I doubt I’ll get an argument from anyone when I say that error handling in T-SQL has been one of its weakest features. Checking for an error code after a statement runs to determine if everything went according to plan is not the best way to handle errors. Fortunately, SQL Server 2005 modernizes error handling with the addition of the TRY/CATCH command blocks.

Listing 9 shows a TRY/CATCH block wrapped around an attempt to delete a record from the Sales.SalesPerson table. Unfortunately, when the code executes, SQL Server will find a reference constraint in place and the record can not be deleted. When the DELETE fails, the CATCH block takes over and displays information pertaining to the error that occurred.

Ranking Functions
The ranking functions provide the ability to add columns that are calculated based on a ranking algorithm. These functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE().

The ROW_NUMBER() function creates a column that displays a number corresponding the row’s position in the query result (see Listing 10). If the column that you specify in the OVER clause is not unique, the ROW_NUMBER() still produces an incrementing column based on the column specified in the OVER clause (see Listing 11).

The RANK() function works much like the ROW_NUMBER() function in that it numbers records in order. When the column specified by the ORDER BY clause contains unique values, then ROW_NUMBER() and RANK() produce identical results. They differ in the way they work when duplicate values are contained in the ORDER BY expression. ROW_NUMBER will increment the numbers by one on every record, regardless of duplicates. RANK() produces a single number for each value in the result set. In Listing 12, the EmpID 164 occurs twice with a ranking value of 1 for both records while EmpID 223 displays with a ranking value of 3.

DENSE_RANK() works the same way as RANK() does but eliminates the gaps in the numbering. In Listing 13, EmpID 164 occurs twice with a ranking value of 1 for both records while EmpID 223 displays with a ranking value of 2 instead of a 3 like RANK() assigned.

NTILE() breaks the result set into a specified number of groups and assigns the same number to each record in a group. In Listing 14, you can see that the result set has been divided into five evenly distributed groups.

The behaviors of all the ranking functions are very similar. Listing 15 provides a side-by-side comparison of them all so you can get a better idea of what their differences are.

DDL Triggers
DML (data manipulation language) triggers have always been an effective way to take action when changes are made to your data. DML permits you to take a number of actions such as updating an audit log of data updates. Triggers, though, are useless when it comes to tracking and auditing changes made to your database schema. For example, suppose one member of your development team adds a new field while another team member changes the size of one field from VARCHAR(20) to VARCHAR(30). In SQL Server 2000 there is no way to track these types of schema updates without the use of a third-party tool. SQL Server 2005 allows you to track such updates with the addition of DDL (data definition language) triggers. As an added bonus, the syntax for DDL triggers is very similar to the syntax for DML triggers.

In Listing 16, a CREATE TRIGGER command creates a DDL trigger to prevent a table from being dropped. The event type (FOR DROP_TABLE) determines which database event this trigger is going to be associated with. To ensure the trigger runs on any DDL change, use DDL_DATABASE_LEVEL_EVENTS.

The EVENTDATA Function

DML triggers use the Inserted and/or Deleted virtual tables to determine the before and after values of an updated record. A DDL trigger uses the EVENTDATA() function to return information about the database event that caused the trigger to fire.

EVENTDATA() returns an XML value and based on the type of event, the XML contains different data. There are four pieces of information that are contained in the XML regardless of the DDL command that fired the trigger.

• The event type
• The time of the event
• The SPID of the connection
• Login and user name information about the individual executing the DDL command

SQL Server Books Online contains more information regarding the data returned by EVENTDATA().

In Listing 17, a CREATE TABLE command creates the AuditLog table and then a CREATE TRIGGER command creates a trigger that adds a record to the AuditLog table whenever any database level event occurs.

Common Table Expressions (CTE)
A Common Table Expression (CTE) is a temporary result set created from a simple query. They are implemented by an expression that creates a table that can be referred to by name. If a CTE sounds like a view or possibly a derived table then you are on the right page. The syntax of a CTE is similar to a view.

A SELECT statement based on data contained in the PurchaseOrderDetail table creates the CTE named PurchaseOrderCTE (see Listing 18). You can see that after the CTE is created another SELECT command draws its rows from the CTE.

Why would you want to use a CTE over a view or derived table? One of the features of a CTE is the ability to create recursive query expressions. Listing 19 uses a CTE to list a manager and all the employees that report to them. It loops through each employee reporting to the manager and then loops through each employee reporting to the current employee and then loops through each employee reporting to the current employee and then, OK, you get the idea. Using recursion, each employee is listed who has employees reporting to them. Recursive queries make working with hierarchal data, such as manager/employee relationships or a Bill of Materials much easier than prior versions of SQL Server.

I hope this gives you a taste of some of the new and exciting T-SQL features being added to SQL Server 2005. I hope this article has piqued your curiosity sufficiently that you will go out and investigate what else SQL Server 2005 has to offer.

If you are interested in getting a jump on learning SQL Server 2005, then you owe it to yourself to pick up a copy of A First Look at SQL Server 2005 for Developers,” by Bob Beauchemin, Niels Berglund, and Dan Sullivan.