I'll begin my review of new T-SQL features and commands with the PIVOT
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 both 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 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.
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.|
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.