SQL Server 2005 T-SQL Enhancements

By Jim Duffy
Published in: : 2005 - January/February
Last updated: February 20, 2019

Published in:

Filed under:

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

In this article I am going to explore some of the new T-SQL features, commands, and capabilities in SQL Server 2005. Because covering everything new in T-SQL would require an entire chapter in a book, I am going to cover some of the more useful and mainstream enhancements.

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.

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.

TOP

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

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.

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.

With SQL Server 2005, the OUTPUT clause has been added so a single trip to the server performs the data update AND returns the results.

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 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 PIVOT operator provides the ability to quickly and easily generate cross tab queries.

The ROW_NUMBER() function creates a column that displays a number corresponding the row's position in the query result (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 (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, 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.

Eventdata

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.

In SQL Server 2000, there is no way to track these types of schema updates without the use of a third-party tool. This changes in SQL Server 2005 with the addition of DDL (data definition language) triggers.

A SELECT statement based on data contained in the PurchaseOrderDetail table creates the CTE named PurchaseOrderCTE (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.

Conclusion

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 sufficiently peaked your curiosity for you to go out and investigate what else SQL Server 2005 has to offer.

Listing 1: Using an expression as the TOP parameter

DECLARE @TopCount AS BIGINT
SET @TopCount = 10

SELECT TOP(@TopCount)
PurchaseOrderID AS POID,
LineNumber AS Line,
ProductID,
OrderQty AS Qty
From Purchasing.PurchaseOrderDetail
WHERE OrderQty > 10

Results:
POID Line ProductID Qty
3 1 530 550
5 1 512 550
6 1 513 550
7 1 317 550
7 2 318 550
7 3 319 550
10 3 322 60
12 1 941 550
13 3 322 60
17 1 523 550

Listing 2: Using the TOP(n) WITH TIES option

SELECT TOP(8) WITH TIES
EmployeeID AS EmpID
,DepartmentID AS DepID
,Gender AS Gen
,BaseRate
FROM HumanResources.Employee
ORDER BY BaseRate DESC;

Results:
EmpID DepID Gen BaseRate
109 16 M 125.50
148 7 M 84.1346
273 3 M 72.1154
12 1 F 63.4615
140 16 F 60.0962
42 11 F 50.4808
158 6 M 50.4808
268 3 M 48.101
284 3 F 48.101
288 3 M 48.101

Listing 3: Using TOP(n) with INSERT, UPDATE, and DELETE

CREATE TABLE TestingTop
(LineNumber INT, OrderQty INT)

/* Using TOP(n) with INSERT */
INSERT TOP(5) TestingTop
SELECT
   LineNumber,
   OrderQty
From Purchasing.PurchaseOrderDetail

SELECT * from TestingTop

Results:
LineNumber OrderQty
1 4
1 3
2 3
1 550
1 3


/* Using TOP(n) with UPDATE */
UPDATE TOP(2) TestingTop
SET OrderQty = 9

SELECT * from TestingTop

Results:
LineNumber OrderQty
1 9
1 9
2 3
1 550
1 3


/* Using TOP(n) with DELETE */
DELETE TOP(2) TestingTop

SELECT * from TestingTop

Results:
LineNumber OrderQty
2 3
1 550
1 3

Listing 4: Utilizing the OUTPUT clause with DELETE

CREATE TABLE OutputTest
(
   KeyID INT Identity,
   Name CHAR(20)
)
GO

INSERT INTO OutputTest (Name)
   Values ('Jim')
INSERT INTO OutputTest (Name)
   Values ('Markus')
INSERT INTO OutputTest (Name)
   Values ('Rod')
INSERT INTO OutputTest (Name)
   Values ('John')
INSERT INTO OutputTest (Name)
   Values ('Alan')
GO

DECLARE @DeletedTable TABLE
(
   DeletedTableID INT,
   DeletedData VARCHAR(20)
)

DELETE OutputTest
OUTPUT
   Deleted.KeyID,
   Deleted.Name
INTO @DeletedTable
WHERE KeyID > 8

SELECT * from @DeletedTable

Results:
DeletedTableID DeletedData
9 John
10 Alan

Listing 5: Utilizing the OUTPUT clause with UPDATE

DECLARE @UpdatedTable TABLE
(
   UpdatedTableID INT,
   OldData VARCHAR(20),
   NewData VARCHAR(20)
)

UPDATE OutputTest
   SET Name = UPPER(Name)
OUTPUT
   Inserted.KeyID,
   Deleted.Name,
   Inserted.Name
INTO @UpdatedTable

SELECT * FROM @UpdatedTable

Results:
UpdatedTableID OldData NewData
6 Jim JIM
7 Markus MARKUS
8 Rod ROD

Listing 6: Creating a monthly PO table with PIVOT

USE AdventureWorks
GO

CREATE TABLE MonthlyPurchaseOrders
(VendorID int NOT NULL,
OrderMonth int NOT NULL,
SubTotal money NOT NULL)
GO

INSERT MonthlyPurchaseOrders
SELECT
   VendorID,
   DATEPART(m, OrderDate),
   SubTotal
From Purchasing.PurchaseOrderHeader
WHERE VendorID IN (3,4,5,6,7,8,9)
GO

SELECT * From MonthlyPurchaseOrders
PIVOT (SUM(SubTotal) FOR OrderMonth In (
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) As X

Results:
VendorID 1 2 3 ... 12
3 64674.225 86232.30 86232.30 64674.225
7 2068.3215 1486.296 1990.2645 1182.6045
4 112289.10 112289.10 112289.10 84216.825
5 343.413 457.884 572.355 343.413
8 1889.0865 1752.975 1185.597 944.6535

Listing 7: Adding PIVOTed records to a table

CREATE TABLE MonthlyPOPivot
(VendorID int NOT NULL,
Jan Money NOT NULL,
Feb Money NOT NULL,
Mar Money NOT NULL)
GO

INSERT MonthlyPOPivot
SELECT * From MonthlyPurchaseOrders
PIVOT (SUM(SubTotal) FOR OrderMonth In (
[1],[2],[3])) As X
GO

SELECT * FROM MonthlyPOPivot

Results:
VendorID Jan Feb Mar
3 64674.225 86232.30 86232.30
7 2068.3215 1486.296 1990.2645
4 112289.10 112289.10 112289.10
5 343.413 457.884 572.355
8 1889.0865 1752.975 1185.597

Listing 8: Creating row-centric data with UNPIVOT

SELECT * FROM MonthlyPOPivot
UNPIVOT (SubTotal FOR MonthName IN ([Jan],[Feb],[Mar])) AS I
ORDER BY VendorID

Results:
VendorID SubTotal MonthName
3 64674.225 Jan
3 86232.30 Feb
3 86232.30 Mar
4 112289.10 Jan
4 112289.10 Feb
4 112289.10 Mar
5 343.413 Jan
5 457.884 Feb
5 572.355 Mar
7 2068.3215 Jan
7 1486.296 Feb
7 1990.2645 Mar
8 1889.0865 Jan
8 1752.975 Feb
8 1185.597 Mar

Listing 9: CATCHing a foreign key volation with TRY/CATCH

BEGIN TRY
    -- Generate a constraint violation error.
   DELETE Sales.SalesPerson WHERE SalesPersonID=275
END TRY
BEGIN CATCH
    SELECT
   ERROR_NUMBER() AS ErrNum,
      ERROR_SEVERITY() AS ErrSev,
      ERROR_STATE() as ErrSt,
      ERROR_MESSAGE() as ErrMsg;
END CATCH
GO

Results:
Error Num ErrSev ErrSt ErrMsg
547 16 0 DELETE statement conflicted with
REFERENCE constraint 'FK_Customer_SalesPerson_SalesPersonID'.
The conflict occurred in database 'AdventureWorks', table
'Customer', column 'SalesPersonID'.

Listing 10: Listing records with a row number based on PurchaseOrderID


SELECT
   PurchaseOrderID,
   EmployeeID,
   ROW_NUMBER()
      OVER(ORDER By PurchaseOrderID) As RowNum
From Purchasing.PurchaseOrderHeader
WHERE ORDERDATE > '01/01/2004'

Results:
PurchaseOrderID EmployeeID RowNum
1313 244 1
1314 231 2
1315 241 3
1316 266 4
1317 164 5
1318 223 6
1319 233 7
1320 238 8
1321 261 9
1322 274 10
... results continue

Listing 11: Listing records with a row number based on EmployeeID


SELECT
   PurchaseOrderID AS POID,
   EmployeeID AS EmpID,
   ROW_NUMBER()
      OVER(ORDER By EmployeeID) As RowNum
From Purchasing.PurchaseOrderHeader
WHERE ORDERDATE > '01/01/2004'

Results:
POID EmpID RowNum
1317 164 1
1327 164 2
1337 164 3
1347 164 4
1367 164 5
1377 164 6
1387 164 7
1397 164 8
1407 164 9
1417 164 10
... results continue

Listing 12: RANKing OVER EmployeeID

SELECT
   PurchaseOrderID AS POID,
   EmployeeID AS EmpID,
   RANK()
      OVER(ORDER By EmployeeID) As Rank
From Purchasing.PurchaseOrderHeader
WHERE PurchaseOrderID < 20

Results:
POID EmpID Rank
5 164 1
15 164 1
6 223 3
16 223 3
2 231 5
12 231 5
7 233 7
17 233 7
8 238 9
18 238 9
3 241 11
13 241 11
... results continue

Listing 13: Listing records with DENSE_RANK OVER EmployeeID

SELECT
   PurchaseOrderID AS POID,
   EmployeeID AS EmpID,
   DENSE_RANK()
      OVER(ORDER By EmployeeID) As DRank
From Purchasing.PurchaseOrderHeader
WHERE PurchaseOrderID < 20

Results:
POID EmpID DRank
5 164 1
15 164 1
6 223 2
16 223 2
2 231 3
12 231 3
7 233 4
17 233 4
8 238 5
18 238 5
3 241 6
13 241 6
... results continue

Listing 14: Listing records and the NTILE() ranks

SELECT
   PurchaseOrderID,
   EmployeeID,
   NTILE(5)
      OVER(ORDER By EmployeeID) As PctTile
From Purchasing.PurchaseOrderHeader
WHERE PurchaseOrderID < 15

Results:
PurchaseOrderID EmployeeID PctTile
5 164 1
6 223 1
2 231 1
12 231 2
7 233 2
8 238 2
3 241 3
13 241 3
1 244 3
11 244 4
9 261 4
4 266 4
14 266 5
10 274 5

Listing 15: A side-by-side comparison of ranking functions

SELECT
   PurchaseOrderID AS POID
   EmployeeID AS EmpID,
   ROW_NUMBER()
      OVER(ORDER By EmployeeID) As RNum,
   RANK()
      OVER(ORDER By EmployeeID) As Rank,
   DENSE_RANK()
      OVER(ORDER By EmployeeID) As DRank,
   NTILE(5)
      OVER(ORDER By EmployeeID) As PctTile
From Purchasing.PurchaseOrderHeader
WHERE PurchaseOrderID < 20

Results:
POID EmpID RNum Rank DRank PctTile
5 164 1 1 1 1
15 164 2 1 1 1
6 223 3 3 2 1
16 223 4 3 2 1
2 231 5 5 3 2
12 231 6 5 3 2
7 233 7 7 4 2
17 233 8 7 4 2
8 238 9 9 5 3
18 238 10 9 5 3
3 241 11 11 6 3
13 241 12 11 6 3
1 244 13 13 7 4
11 244 14 13 7 4
9 261 15 15 8 4
19 261 16 15 8 4
4 266 17 17 9 5
14 266 18 17 9 5
10 274 19 19 10 5

Listing 16: Creating a DDL trigger to prevent dropping tables

CREATE TABLE TestDLLTriggers
(KeyID INT NOT NULL)
GO

CREATE TRIGGER NotSoFast
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Not so fast! Who said you could drop a table?'
ROLLBACK

Drop Table TestDLLTriggers

Not so fast there wise guy! Who said you could drop a table?
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

Listing 17: Implementing a DDL Auditing table

CREATE TABLE AuditLog
(
AuditLogID INT PRIMARY KEY IDENTITY,
PostedTime DATETIME,
UserData VARCHAR(100),
DatabaseEvent VARCHAR(100),
SQLCmd VARCHAR(2000)
)
GO

CREATE TRIGGER DDLAuditTrigger
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @Data XML
SET @Data = EventData()

INSERT INTO AuditLog
(
PostedTime,
UserData,
DatabaseEvent,
SQLCmd
)
VALUES (
GetDate(),
CONVERT(VARCHAR(100),CURRENT_USER),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
GO

Listing XX
CREATE TABLE TestDDLAudit
(Field1 Int NOT NULL)
GO

SELECT * FROM AuditLog

Results:
LogID Post UserData DBEvent SQLCmd
1 2004-11-01... dbo CREATE_TABLE CREATE TABLE TestDDLAudit (Field1 Int NOT NULL)

Listing 18: Using CTE as a derived table and filtered

WITH PurchaseOrderCTE(ProductID, PurchaseOrderID)
AS (
   SELECT ProductID, Count(PurchaseOrderID)
      From Purchasing.PurchaseOrderDetail
      GROUP BY ProductID
)

SELECT * FROM PurchaseOrderCTE
   WHERE PurchaseOrderID > 100

Results:
ProductID PurchaseOrderID
319 130
320 125
321 125
322 124
356 101
357 101
491 101
507 102
More results...

Listing 19: List of managers and their employees created from a CTE recursive query expression

WITH ManagedEmployees(ManagerID, EmployeeID, EmployeeLevel)
AS (
    SELECT E.ManagerID, E.EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee E
    WHERE E.ManagerID IS NULL
    UNION ALL
    SELECT E2.ManagerID, E2.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee E2
        INNER JOIN ManagedEmployees M
        ON E2.ManagerID = M.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM ManagedEmployees ;
GO

ManagerID EmployeeID EmployeeLevel
NULL 109 0
109 6 1
109 12 1
109 21 1
109 42 1
109 140 1
109 148 1
109 273 1
273 268 2
273 284 2
273 288 2
288 290 3
284 285 3
284 286 3
284 289 3
268 275 3
268 276 3
268 277 3
268 278 3
268 279 3
268 280 3
268 281 3
268 282 3
268 283 3
268 287 3
148 218 2
218 49 3
218 225 3
49 242 4
49 251 4
49 259 4
49 260 4
140 30 2
140 71 2
140 103 2
140 139 2
139 59 3
139 94 3
139 130 3
139 166 3
More results...

This article was filed under:

This article was published in:

Have additional technical questions?

Get help from the experts at CODE Magazine - sign up for our free hour of consulting!

Contact CODE Consulting at techhelp@codemag.com.