Learn SQL Server 2005 T-SQL Enhancements

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

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.

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.

Next Page