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
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
, 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
|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.|
In SQL Server 2000, when you wanted to determine the changes from an INSERT
, 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
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
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.