The Baker's Dozen: 13 Productivity Tips for Transact-SQL 2005

icrosoft implemented many new features in SQL Server 2005, including an impressive set of language enhancements. From new language statements for SQL-99 compatibility to new features in response to customer requests, Transact-SQL 2005 helps to increase developer productivity. This article covers most of the new language features by posing a statement/scenario and then provide some code samples to show how you can use T-SQL 2005 to address the problem. You'll also get a brief glance at Visual Studio Team Edition for Database Professionals, a product that helps a development team to manage databases. Finally, I'll give you a sneak preview of some features in the next scheduled version of SQL Server (SQL Server 2008, "Katmai").

Beginning with the End in Mind
I speak at community events (MSDN Code Camp, user groups, etc.) at least once a month. One of the more popular sessions (as well as one of my favorites) is called "T-SQL for Developers."

I'm a big believer in plenty of code samples to demonstrate functionality, and so my goal is to provide a healthy number of code samples for each of the following:

The PIVOT statement

Common table expressions (CTEs) and recursive queries (part 1 of 2)

CTEs and recursive queries (part 2 of 2)

OUTPUT and OUTPUTINTO, to gain immediate access to the system INSERTED and DELETED tables

Isolation levels (part 1 of 2)

Isolation levels (part 2 of 2)

New XQUERY capabilities to handle variable number of selections

Variable TOP N APPLY and table-valued UDFs

RANKING and PARTITIONING

New TRY…CATCH capabilities and RAISING errors

INTERSECT/EXCEPT

Flexible UPDATE procedures

All code samples will work using the AdventureWorks database that comes with SQL Server 2005.

Tip 1: PIVOTScenario: You want to query a table of vendor orders and group the order amounts by quarter for each vendor.

Application developers often need to convert raw data into some type of analytical view, such as sales by month or quarter or the brackets of an aging report. Prior to SQL Server 2005, you would often have to examine each row with a CASE statement to place raw data into a column.

Application developers often need to convert raw data into a result set that represents an analytical view. The PIVOT statement makes this task much easier.

SQL Server 2005 introduced the PIVOT statement, arguably the most well-known new language feature. PIVOT allows you to (as the name implies) turn rows of raw data into columns. The code below shows a basic example for PIVOT: a query against the Purchase Order tables in AdventureWorks that summarizes order amounts by quarter:

USE AdventureWorks
GO
WITH OrdCTE AS (
SELECT VendorID, DatePart(q,OrderDate) AS OrderQtr,
(OrderQty * UnitPrice) AS OrderTot
FROM Purchasing.PurchaseOrderHeader POHdr
JOIN Purchasing.PurchaseOrderDetail PODtl
ON POHdr.PurchaseOrderID = PODtl.PurchaseOrderID )
SELECT VendorID,[1] AS Q1,[2] AS Q2,[3] AS Q3,[4] AS Q4 FROM OrdCTE
PIVOT (SUM(OrderTot) FOR OrderQtr IN ([1],[2],[3],[4])) AS X
-- You can use MAX instead, if you want the top order for each Qtr

Note the syntax for the PIVOT statement: You essentially need to tell PIVOT three pieces of information:

Which column you are pivoting on (OrderTot).

Which column you want to examine (OrderQtr, from the Quarter DatePart of the OrderDate), to determine how to pivot.

The possible values of the column you want to examine (the only possible values of a Quarter DatePart are 1, 2, 3, or 4):

SELECT VendorID,
[1] AS Q1,[2] AS Q2,
[3] AS Q3,[4] AS Q4
FROM OrdCTE
PIVOT (SUM(OrderTot) FOR OrderQtr IN
([1],[2],[3],[4])) AS X

A few additional notes on PIVOT:

The list of values in the IN clause must be static. Microsoft's implementation of PIVOT does not directly support dynamic queries. If you need to determine these values dynamically at runtime, you must construct the entire SQL statement as a string and then use Dynamic SQL. If you frequently need to generate PIVOT tables dynamically, you may want to look at GeckoWare's SQL CrossTab Builder product.

You must specify the column you are pivoting on (Ordertot in this case) as a scalar expression (e.g. MAX(), SUM(), etc.).

Note that summary example in this section contained a new language construct: WITH (name). This is a common table expression (CTE), which you can think of as a dynamic view. I'll cover CTEs in the next few tips.