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 OUTPUT INTO, 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
- Flexible UPDATE procedures
All code samples will work using the AdventureWorks database that comes with SQL Server 2005.
Tip 1: PIVOT
Scenario: 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:
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, AS Q1, AS Q2, AS Q3, AS Q4 FROM OrdCTE
PIVOT (SUM(OrderTot) FOR OrderQtr IN (,,,)) 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):
 AS Q1, AS Q2,
 AS Q3, AS Q4
PIVOT (SUM(OrderTot) FOR OrderQtr IN
(,,,)) 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.