Database Design

Take Advantage of New T-SQL Features

T-SQL includes several new capabilities that will add punch to your database apps, including TRY...CATCH error handling, pivot aggregate values to create crosstab tables, and more.

Technology Toolbox: VB.NET, SQL Server 2005, Visual Studio 2005/Visual Basic Express Edition, ADO.NET 2.0

SQL Server 2005's five-year gestation period gave Microsoft's development team the opportunity to implement an extraordinary number of new features that DBAs and database developers will find useful.

Recent articles have covered SQL Server 2005's native XML data type, SQL Server 2005 Express Edition, and column-level or cell-scoped encryption. In this article, I'll describe new T-SQL keywords that let you substitute TRY...CATCH structure for the @@ERROR function, pivot aggregate rowsets to generate crosstab reports, add row numbers with ranking and windowing functions, and create temporary in-memory tables with common table expressions. I'll also demonstrate new modifiers that greatly enhance SQL Server 2000 FOR XML queries. Most T-SQL batch statement examples are included in this article's sample code. The sample code requires installing the Northwind sample database to your SQL Server 2005 or SQL Express instance. By default, expanding the ZIP file creates a \TSQL2005 folder to contain the sample T-SQL scripts, which you can open and execute in SQL Server Management Studio or SQL Express Manager.

New BEGIN TRY ... END TRY and BEGIN CATCH ... END CATCH blocks let you emulate structured exception handling in T-SQL batch queries and stored procedures. The five ERROR_* functions return much more information about the error than the @@ERROR function's error number:

BEGIN TRY
   -- Batch statements;
END TRY
BEGIN CATCH
   -- Error-handling statements, typically
   SELECT ERROR_NUMBER() AS ErrorNumber,
      ERROR_PROCEDURE AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The BEGIN CATCH statement must be the next line after the END TRY instruction. You can nest TRY...CATCH blocks with this structure:

BEGIN TRY
   -- Outer-level statements
END TRY
BEGIN CATCH
   -- Outer-level error-handling statements
   BEGIN TRY
   -- Inner-level statements
   END TRY
   BEGIN CATCH
   -- Inner-level error-handling statements
   END CATCH
END CATCH

Run this script to illustrate T-SQL's new error-handling functionality:

BEGIN TRAN
GO
BEGIN TRY
   -- Causes a constraint violation on the 
   -- Order Details table.
   DELETE FROM Products 
      WHERE ProductID = 15
   COMMIT TRAN
END TRY
BEGIN CATCH
   ROLLBACK TRAN
   SELECT ERROR_NUMBER() AS ErrorNumber,
      ERROR_LINE() AS ErrorLine,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_MESSAGE() as ErrorMessage
END CATCH
GO

Executing the script (TryCatchBlocks.sql) throws this error message: "The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "Order Details", column 'ProductID'."

Implement Pivots in T-SQL
Developers upsizing from Jet to SQL Server databases have complained long and loud about T-SQL's lack of PIVOT and TRANSFORM keywords for writing crosstab queries. SQL Server 2005 finally implements PIVOT, but not TRANSFORM. PIVOT requires a FOR operator and an IN predicate that contains the equivalent of Jet's fixed column headings. The FOR operator doesn't support an expression as the PIVOT argument. Here's the generic PIVOT operator syntax:

SELECT RowHeader1, RowHeader2, ... 
   ColValue1 AS ColHeader1, 
   ColValue2 AS ColHeader2, 
   ColValue3 AS ColHeader3, ...
FROM TableName
PIVOT (Aggregate(ValueColName) 
   FOR ValueSourceColName 
   IN(ColValue1, ColValue2, ColValue3, ...))

The OrdersByProduct1997.sql sample T-SQL script creates a rollup table from the Northwind database's online transaction processing (OLTP) records. The OrdersByProduct1997 source table has these columns: CategoryName (nvarchar(20)), ProductName (nvarchar(40)), Quarter (int), and ProductOrders (money) (see Figure 1). A PIVOT expression (OrdersByProduct1997Pivot.sql) returns a crosstab rowset with columns that report quarterly and annual sales for 1997 (see Figure 2):

IF OBJECT_ID (N'OrdersByProduct1997Pivot', N'U') 
   IS NOT NULL 
   DROP TABLE dbo.OrdersByProduct1997Pivot 
GO
CREATE TABLE dbo.OrdersByProduct1997Pivot 
   (Category nvarchar(20), Product nvarchar(40), 
   Y1997Q1 money, Y1997Q2 money, Y1997Q3 
   money, Y1997Q4 money, Y1997Totals money)
GO

INSERT dbo.OrdersByProduct1997Pivot(Category, 
   Product, Y1997Q1, Y1997Q2, Y1997Q3, Y1997Q4) 
   SELECT CategoryName AS Category, 
      ProductName AS Product, [1] AS Y1997Q1, 
      [2] AS Y1997Q2, [3] AS Y1997Q3, [4] AS Y1997Q4
   FROM dbo.OrdersByProduct1997
   PIVOT (SUM(ProductOrders) FOR 
      Quarter IN([1], [2], [3], [4])) AS QuarterlyOrders 
   ORDER BY CategoryName, ProductName
GO

UPDATE dbo.OrdersByProduct1997Pivot 
SET Y1997Totals = ISNULL(Y1997Q1, 0) + 
   ISNULL(Y1997Q2, 0) + ISNULL(Y1997Q3, 0) + 
   ISNULL(Y1997Q4, 0)
GO
SELECT * FROM dbo.OrdersByProduct1997Pivot

This PIVOT expression (in bold) creates and inserts rows in an OrdersByProduct1997Pivot table. The UPDATE statement computes row totals by substituting 0 for NULL ProductOrders values. Updating all NULL values to 0 lets you cross-foot the resultset by inserting a row with the SUMs of the five numeric columns.

The persistent table lets you test UNPIVOT operator syntax (OrdersByProduct1997Unpivot.sql) to return a rowset that's identical to the OrdersByProduct1997 source table:

SELECT Product AS ProductName, Category 
   AS CategoryName, Quarter, ProductOrders 
FROM (SELECT Category, Product, Y1997Q1 AS [1], 
   Y1997Q2 AS [2],   Y1997Q3 AS [3], Y1997Q4 AS [4] 
   FROM dbo.OrdersByProduct1997Pivot) AS P1
   UNPIVOT (ProductOrders 
      FOR Quarter IN([1], [2], [3], [4])) 
      AS QuarterlyOrders 
GO

Note that the UNPIVOT query's sub-SELECT statement that forms the first FROM clause is the PIVOT query's SELECT clause with the crosstab column names and aliases interchanged. The UNPIVOT clause is the PIVOT clause with the SUM aggregate function removed.

Use Ranking Functions and Windowing Clauses
The T-SQL TOP (n) [PERCENT] operator introduced DBAs and developers to rowsets based on the ranking of the rows that's determined by the sort order that you specify with an ORDER BY clause. SQL Server required n to be a literal integer or—with the PERCENT modifier—a float value. SQL Server 2005 expands on this by letting you substitute a variable of the bigint or float data type for the literal numerical value:

DECLARE @numRows AS bigint;
SET @numRows = 10;
SELECT TOP (@numRows) * FROM Products
   ORDER BY UnitPrice DESC
GO
DECLARE @pctRows AS float;
SET @pctRows = 12.5;
SELECT TOP (@pctRows) PERCENT * FROM Products
   ORDER BY UnitPrice DESC
GO

This batch (TopProductsByPrice.sql) returns two rowsets with rows for the 10 most expensive products. Adding the WITH TIES modifier includes rows having UnitPrice values that match the last row returned by the TOP expression, regardless of the numRows or pctRows value.

For example, this batch (Top11ProductsByPriceWithTies.sql) returns 12 rows because the UnitPrice values of rows 11 and 12 are the same ($43.90):

DECLARE @numRows AS bigint;
SET @numRows = 11;
SELECT TOP (@numRows) WITH TIES * 
FROM Products 
ORDER BY UnitPrice DESC
GO

The ANSI SQL99 ROW_NUMBER, RANK, and DENSE RANK functions return bigint values that correspond to the order of the rows you specify by an associated ORDER BY clause. ROW_NUMBER returns a monotonically increasing value for all rows in the specified rowset (see Figure 3). RANK and DENSE RANK return duplicate numbers for values with ties. RANK has gaps in the row number value following ties; DENSE RANK eliminates the ties. This batch returns RANK, DENSE RANK, and ROW_NUMBER values for the Products table in descending UnitPrice order:

SELECT ProductID, ProductName, UnitPrice, 
   RANK() OVER (ORDER BY UnitPrice DESC) 
      AS Rank,
   DENSE_RANK() OVER (ORDER BY UnitPrice 
      DESC) AS DenseRank,
   ROW_NUMBER() OVER (ORDER BY UnitPrice 
      DESC) AS RowNumber
FROM Products
GO

The ORDER BY clause for ROW_NUMBER returns nondeterministic values for rows with equal Unit Price values, such as 11 and 12, and 15 and 16. A non-deterministic rowset has values that can differ for successive query invocations. For example, Schoggi Schokolade and Vegie-spread are equally valid as ROW_NUMBER 11 and 12 or 12 and 11, respectively. You must add another column to the ORDER BY clause, such as ProductID ASC, to generate a deterministic rowset in which ROW_NUMBERS are guaranteed to be consistent for single-column value ties.

PARTITION BY is a windowing clause that divides a ranked resultset into groups that you specify. This batch groups rankings by product Category partitions (ProductsPartition.sql):

SELECT CategoryID, ProductID, ProductName, 
   UnitPrice, RANK() OVER (PARTITION BY 
   CategoryID ORDER BY UnitPrice DESC) AS 
   Rank, DENSE_RANK() OVER (PARTITION BY 
   CategoryID ORDER BY UnitPrice DESC) AS 
   DenseRank, ROW_NUMBER() OVER 
   (PARTITION BY CategoryID ORDER BY 
   UnitPrice DESC) AS RowNumber
FROM Products
GO

Adding PARTITION BY is similar to applying GROUP BY to the SELECT statement, but you can apply a different PARTITION BY criterion to each ranking function in the query. The ranking values start over for each PARTION BY criterion. NTILE(buckets) assigns a rank of 1 through buckets to the rows; for example, if buckets = 4, each row has a quartile value (1 through 4).

Common Table Expressions (CTEs) are temporary, in-memory tables that are easier to create and populate than conventional temporary tables. CTEs are similar to derived tables, but you can reference CTEs by name and use them more than once. CTEs enable recursive queries that you define with a UNION ALL query that combines rows from an anchor member and a recursive member (EmployeesDirectReportsCTE.sql):

WITH DirectReports (Name, EmployeeID, ReportsTo) AS
--Anchor member
(SELECT FirstName + ' ' + LastName, EmployeeID, 
ReportsTo FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--Recursive member
SELECT emp.FirstName + ' ' + emp.LastName, 
emp.EmployeeID, emp.ReportsTo
FROM Employees emp INNER JOIN DirectReports dr
ON emp.ReportsTo = dr.EmployeeID)

SELECT * FROM DirectReports;
GO

This batch returns Andrew Fuller (2), who has no manager, followed by employees who report to him and those who report to Steven Buchanan (5).

You can replace persistent and temporary tables with CTEs in some cases. For example, this batch (OrdersByProduct1997PivotCTE.sql) generates a PIVOT rowset from a CTE rollup table:

WITH cteRollup (ProductName, CategoryName, 
   Quarter, ProductOrders) AS
(SELECT ProductName, CategoryName, 
   DatePart(quarter, OrderDate) AS Quarter, 
   CONVERT(money, SUM([Order 
   Details].UnitPrice * 
   Quantity * (1-Discount))) AS ProductOrders
FROM Categories INNER JOIN (Products INNER 
   JOIN (Orders INNER JOIN [Order Details] 
   ON Orders.OrderID = [Order Details].OrderID) 
   ON Products.ProductID = [Order 
   Details].ProductID) ON Categories.CategoryID = 
   Products.CategoryID
WHERE OrderDate BETWEEN '1/1/1997' And 
   '12/31/1997'
GROUP BY ProductName, CategoryName, 
   DatePart(quarter, OrderDate))

SELECT CategoryName AS Category, ProductName 
   AS Product, [1] AS Y1997Q1, [2] AS Y1997Q2, 
   [3] AS Y1997Q3, [4] AS Y1997Q4
FROM cteRollup
PIVOT (SUM(ProductOrders) 
FOR Quarter IN([1], [2], [3], [4])) AS QuarterlyOrders
ORDER BY CategoryName, ProductName;

Customize FOR XML Queries
SQL Server 2005 adds TYPE, XMLSCHEMA('namespace'), and ROOT('elementName') directives to FOR XML queries, as well as a new FOR XML PATH mode and an XSINIL parameter for the ELEMENTS directive. These new keywords expand the ability to customize the structure and content of XML documents you generate with FOR XML AUTO, FOR XML RAW, and FOR XML PATH queries.

The TYPE directive specifies that a FOR XML AUTO query returns the XML document, fragment, or node in the new native XML data type instead of an nvarchar string. Specifying FOR XML AUTO[, ELEMENTS], TYPE also lets you generate nested XML documents or fragments without resorting to FOR XML EXPLICIT mode queries, which some developers call "XML queries from hell."

Generating nested XML documents or fragments require outer and inner SELECT queries, each of which must include a FOR XML AUTO[, ELEMENTS], TYPE clause (NestedQueryType.sql):

SELECT TOP 10 OrderID, CustomerID, EmployeeID, 
OrderDate, 
   (SELECT ProductID,    CONVERT(decimal(6,2), 
   UnitPrice) AS UnitPrice, Quantity, 
   CONVERT (decimal(3,3), Discount) AS Discount 
   FROM [Order Details] AS OrderDetail 
   WHERE OrderDetail.OrderID = Orders.OrderID 
   FOR XML AUTO, ELEMENTS, TYPE)
FROM Orders AS Order
ORDER BY OrderID DESC
FOR XML AUTO, ELEMENTS, TYPE
GO

The query generates 10 XML fragments (NestedQueryType.xml), which look something like this:

<Orders>
   <OrderID>11102</OrderID>
   <CustomerID>GREAL</CustomerID>
   <EmployeeID>5</EmployeeID>
   <OrderDate>2005-05-31T00:00:00</OrderDate>
   <OrderDetail>
      <ProductID>11</ProductID>
      <UnitPrice>10.00</UnitPrice>
      <Quantity>10</Quantity>
      <Discount>0.075</Discount>
   </OrderDetail>
   <OrderDetail>
      <ProductID>12</ProductID>
      <UnitPrice>10.00</UnitPrice>
      <Quantity>10</Quantity>
      <Discount>0.075</Discount>
   </OrderDetail>
   ...
</Orders>
...

If you omit the ELEMENTS directive of either or both queries, the <Orders>, <Order Details>, or both nodes change from element-centric to attribute-centric structure. Columns of the native XML data type accept well-formed XML documents or fragments. If you omit the two TYPE directives (NestedQueryElements.sql), the OrderDetail elements revert from nested elements to string format with < and > escaped to < and &lgt; (NestedQueryElements.xml).

FOR XML RAW MODE lets you specify custom node names, add a custom root element to convert XML fragments to well-formed documents, and wrap multiple child node with grouping element(s). This query (NestedQueryTypeRaw.sql) changes <Orders> tags to <Order>, adds an <Orders> root element, uses the XSINIL parameter to replace NULL ShippedDate values with xsi:nil="true", changes <Order_Details> tags to <OrderDetail>, and adds an <OrderDetails> grouping element to <OrderDetail> nodes:

SELECT TOP 10 OrderID, CustomerID, EmployeeID, 
OrderDate, ShippedDate,
   (SELECT ProductID, CONVERT(decimal(6,2), 
   UnitPrice) as UnitPrice, Quantity,
   CONVERT (decimal(3,3), Discount) As Discount
   FROM [Order Details]
   WHERE [Order Details].OrderID = 
      Orders.OrderID
   FOR XML RAW('OrderDetail'), ELEMENTS, 
      TYPE, ROOT('OrderDetails'))
FROM Orders
WHERE OrderID < 11072
ORDER BY OrderID DESC
FOR XML RAW('Order'), ELEMENTS XSINIL, TYPE, 
   ROOT('Orders')
GO

The NestedQueryTypeRaw.sql query generates this abbreviated document structure:

<Orders xmlns:xsi=".../2001/XMLSchema-instance">
   <Order>
      <OrderID>11071</OrderID>
      <CustomerID>LILAS</CustomerID>
      <EmployeeID>1</EmployeeID>
      <OrderDate>1998-05-07T...</OrderDate>
      <ShippedDate xsi:nil="true" />
      <OrderDetails>
         <OrderDetail>
            <ProductID>7</ProductID>
            <UnitPrice>30.00</UnitPrice>
            <Quantity>15</Quantity>
            <Discount>0.050</Discount>
         </OrderDetail>
         ...
      </OrderDetails>
   </Order>
   ...
</Orders>

FOR XML PATH mode enables adding column values as element or attribute values, creating group subelements, and adding subelements to groups. XPath syntax specifies column values as attributes of the outer query's top element with AS @AttributeName or subelements of the top element with AS ElementName. Use AS SubgroupName/ElementName or AS SubgroupName/@AttributeName expressions for subgroups. This FOR XML PATH query (OrdersPathSample2.sql) returns OrderID, CustomerID, and EmployeeID values as attributes of the <Order> group and SKU (ProductID) as an attribute of the <LineItem> group:

SELECT TOP 10 OrderID AS [@OrderID], 
CustomerID AS [@CustomerID], EmployeeID AS 
[@EmployeeID], OrderDate, ShippedDate,
   (SELECT Products.ProductID AS [@SKU], 
   Quantity, ProductName AS Product, 
   QuantityPerUnit AS Package, 
   CONVERT(decimal(6,2), 
   [Order Details].UnitPrice) AS ListPrice,
   CONVERT(decimal(3,3), Discount) AS Discount
   FROM [Order Details], Products
   WHERE [Order Details].OrderID = 
   Orders.OrderID AND Products.ProductID = 
   [Order Details].ProductID
   FOR XML PATH('LineItem'), TYPE, 
   ROOT('LineItems'))
FROM Orders
WHERE OrderID < 11077
ORDER BY OrderID DESC
FOR XML PATH('Order'), ELEMENTS XSINIL, TYPE, 
   ROOT('Orders')

Changing primary key (ID) values from elements to attributes classifies keys as XML document metadata and the remaining column values as content. The OrdersPathSample2.sql query creates a document with this structure:

<Orders xmlns:xsi="... /2001/XMLSchema-instance">
   <Order OrderID="11076" CustomerID="BONAP" 
         EmployeeID="4">
      <OrderDate>1998-05-08T...</OrderDate>
      <ShippedDate xsi:nil="true" />
      <LineItems>
         ...
         <LineItem SKU="14">
            <Quantity>20</Quantity>
            <Product>Tofu</Product>
            <Package>
               40 - 100 g pkgs.
            </Package>
            <ListPrice>23.25</ListPrice>
            <Discount>0.250</Discount>
         </LineItem>
         ...
      </LineItems>
   </Order>
   ...
</Orders>

FOR XML PATH queries rival the capabilities of more complex object-relational mapping schemes, such as ObjectSpaces (formerly code-named "Orca," an acronym for "Object-relational component architecture"), which Microsoft removed from VS 2005 and the .NET Framework 2.0 and then promised to merge with the subsequently postponed WinFS. It's much easier to write custom FOR XML PATH queries than the corresponding three-part mapping files to generate typical business documents from OLTP tables (see Figure 4).

Publishing limitations prevent a single article from covering all but a few of the new T-SQL keywords added by SQL Server 2005. Stay tuned for future Database Design columns on other new SQL Server 2005 features enabled by T-SQL's rapidly expanding vocabulary, including Service Broker, Query Notifications, and Native XML Web services.

comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.