Cool Features in SQL Server 2008

Cool Features in SQL Server 2008 The PIVOT and UNPIVOT Operators Let's face it—users usually want to see data in tabular format, which is a bit of a challenge given that data in SQL Server is most often stored in a highly relational form. PIVOT is a T-SQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional crosstab query. Using PIVOT is easy. In your SELECT statement, you specify the values you want to pivot on. The following example in the AdventureWorks2008 database uses the order years (calculated using the DatePart function) as the columns. The FROM clause looks normal except for the PIVOT statement. This statement creates the value you want to show in the rows of the newly created columns. This example uses the aggregate SUM of TotalDue (a calculated field in the FROM clause). Then we use the FOR operator to list the values we want to pivot on in the OrderYear column. The example is shown in Listing 2-11. Listing 2-11. Creating tabular results with the PIVOT operator SELECT CustomerID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004 FROM ( SELECT CustomerID, DATEPART(yyyy, OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS piv PIVOT ( SUM(TotalDue) FOR OrderYear IN([2001], [2002], [2003], [2004]) ) AS child ORDER BY CustomerID Here are the results: CustomerID Y2001 Y2002 Y2003 Y2004 ----------- ---------- ----------- ------------ ------------ 1 40732.6067 72366.1284 NULL NULL 2 NULL1 5653.6715 12118.0275 4962.2705 3 39752.8421 168393.7021 219434.4265 51925.3549 4 NULL 263025.3113 373484.299 143525.6018 5 NULL 33370.6901 60206.9999 20641.1106 6 NULL NULL 668.4861 2979.3473 7 NULL 6651.036 3718.7804 NULL 8 NULL NULL 19439.2466 10900.0347 9 NULL 320.6283 11401.5975 5282.8652 10 NULL 96701.7401 291472.2172 204525.9634 ... That's all there is to it. Of course, this example is simplified to show you the concept; other, more sophisticated, aggregates are possible, and you can even use CTEs in the FROM clause. In any case, using PIVOT is simple. Using UNPIVOT You can use the UNPIVOT operator to normalize data that is already pivoted. For example, suppose you obtain pivoted data that shows, for each vendor, the number of orders placed by each employee. The code in Listing 2-12 creates such a table. Listing 2-12. Example table containing pivoted data CREATE TABLE VendorEmployee (VendorID int, Emp1Orders int, Emp2Orders int, Emp3Orders int, Emp4Orders int, Emp5Orders int) GO INSERT INTO VendorEmployee VALUES(1, 4, 3, 5, 4, 4) INSERT INTO VendorEmployee VALUES(2, 4, 1, 5, 5, 5) INSERT INTO VendorEmployee VALUES(3, 4, 3, 5, 4, 4) INSERT INTO VendorEmployee VALUES(4, 4, 2, 5, 4, 4) INSERT INTO VendorEmployee VALUES(5, 5, 1, 5, 5, 5) Our table looks like this: VendorID Emp1Orders Emp2Orders Emp3Orders Emp4Orders Emp5Orders ----------- ----------- ----------- ----------- ----------- ----------- 1 4 3 5 4 4 2 4 1 5 5 5 3 4 3 5 4 4 4 4 2 5 4 4 5 5 1 5 5 5 You might want to unpivot the data to display columns for vendor ID, employee, and number of orders. Listing 2-13 shows how to use the UNPIVOT operator to achieve this goal. Listing 2-13. Using the UNPIVOT operator SELECT VendorId, Employee, Orders AS NumberOfOrders FROM (SELECT VendorId, Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders FROM VendorEmployee ) AS p UNPIVOT ( Orders FOR Employee IN (Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders) ) AS unpvt Here are the results: VendorID Employee NumberOfOrders --------- ------------ ---------------------- 1 Emp1Orders 4 1 Emp2Orders 3 1 Emp3Orders 5 1 Emp4Orders 4 1 Emp5Orders 4 2 Emp1Orders 4 ... Dynamically Pivoting Columns The problem with PIVOT is the same problem with CASE and other methods: you have to specify the columns. Consider the code in Listing 2-14. Listing 2-14. Statically driven PIVOT SELECT * FROM (SELECT CustomerID, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader) AS header PIVOT ( SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004]) ) AS piv The results show us a nice crosstab query with the years displayed as columns: CustomerID 2002 2003 2004 ----------- ---------- ---------- ----------- 14324 NULL 2264.2536 3394.9247 22814 NULL 5.514 NULL 11407 NULL 59.659 NULL 28387 NULL NULL 645.2869 19897 NULL NULL 659.6408 15675 2699.9018 2682.9953 2580.1529 24165 NULL 2699.9018 666.8565 ... Because this data goes only up to 2004, what happens when you add 2005 to the data? Do you want to go into all your queries and add the current year to the IN clause? We can accommodate new years in the data by dynamically building the IN clause and then pro-grammatically writing the entire SQL statement. Once you have dynamically written the SQL statement, you can execute it using sp_executesql, as shown in Listing 2-15. Since all we have to do is generate dynamically the IN clause, creating a dynamic PIVOT in SQL Server is much easier than creating a dynamic CASE statement. The results are exactly the same as those shown following Listing 2-14, except that as new yearly data is added to the table, the query dynamically adds the column for it. Remember that your reporting engine will most likely not accommodate the new dynamic columns, but data-bound controls will. Listing 2-15. Dynamically driven PIVOT DECLARE @tblOrderDate AS TABLE(y int NOT NULL PRIMARY KEY) INSERT INTO @tblOrderDate SELECT DISTINCT YEAR(OrderDate) FROM Sales.SalesOrderHeader DECLARE @cols AS nvarchar(max) DECLARE @years AS int SET @years = (SELECT MIN(y) FROM @tblOrderDate) SET @cols = N'' WHILE @years IS NOT NULL BEGIN SET @cols = @cols + N',[' + CAST(@years AS nvarchar(max)) + N']' SET @years = (SELECT MIN(y) FROM @tblOrderDate WHERE y > @years) END SET @cols = SUBSTRING(@cols, 2, LEN(@cols)) DECLARE @sql AS nvarchar(max) SET @sql = ' SELECT * FROM (SELECT CustomerId, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS a PIVOT ( SUM(TotalDue) FOR OrderYear IN(' + @cols + N') ) AS b ' PRINT @sql -- for debugging EXEC sp_executesql @sql You can accomplish the same results using the newer CTE syntax instead of using the table variable, as shown in Listing 2-16. Listing 2-16. Dynamically driven PIVOT using a CTE DECLARE @cols AS nvarchar(MAX) WITH YearsCTE AS (SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader) SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS nvarchar(10)) + ']' FROM YearsCTE ORDER BY [YEAR] -- Construct the full T-SQL statement and execute it dynamically. DECLARE @sql AS nvarchar(MAX) SET @sql = ' SELECT * FROM (SELECT CustomerId, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS a PIVOT ( SUM(TotalDue) FOR OrderYear IN(' + @cols + N') ) AS b ' PRINT @sql -- for debugging EXEC sp_executesql @sql The APPLY Operator APPLY is an operator that you specify in the FROM clause of a query. It enables you to invoke a table-valued function (TVF) for each row of an outer table. The flexibility of APPLY is evident when you use the outer table's columns as your function's arguments. The APPLY operator has two forms: CROSS APPLY and OUTER APPLY. CROSS APPLY doesn't return the outer table's row if the TVF returns an empty set for it; the OUTER APPLY returns a row with NULL values instead of the function's columns. To see how APPLY works, we'll first create a TVF that returns a table. Listing 2-17 shows a simple function that returns as a table the top n rows for a customer from the SalesOrderHeader table. Listing 2-17. Returning a table CREATE FUNCTION fnGetCustomerOrders(@CustomerID int, @TopRecords bigint) RETURNS TABLE AS RETURN SELECT TOP (@TopRecords) * FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID ORDER BY OrderDate DESC After creating the fnGetCustomerOrders TVF, we call it from the query, as shown in Listing 2-18. Listing 2-18. Executing a query with APPLY SELECT * FROM Sales.Customer cust CROSS APPLY fnGetCustomerOrders(CustomerID, 100) This query returns all the records from the Customers table and then, as additional fields, the records from the Orders table (by way of the fnGetCustomerOrders function) that match for the customer ID because that's what's being passed in dynamically as the first argument to fnGetCustomerOrders. Because we passed the value 100 for the second parameter, rows for up to the first 100 orders per customer are generated and returned by this query.

Exception Handling in Transactions SQL Server offers major improvements in error handling inside T-SQL transactions. As of SQL Server 2005, you can catch T-SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context. The only types of errors that the TRY/CATCH construct can't handle are those that cause the termination of your session (usually errors with severity 21 and above, such as hardware errors). The syntax is shown here: BEGIN TRY --sql statements END TRY BEGIN CATCH --sql statements for catching your errors END CATCH If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely skipped. You can investigate the type of error that was raised and react accordingly. To do so, you can use the ERROR_xxx functions to return error information in the CATCH block, as shown in Listing 2-35. Listing 2-35. T-SQL exception handling example BEGIN TRY SELECT 5/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrNumber, ERROR_SEVERITY() AS ErrSeverity, ERROR_STATE() AS ErrState, ERROR_PROCEDURE() AS ErrProc, ERROR_LINE() AS ErrLine, ERROR_MESSAGE() AS ErrMessage END CATCH You can examine the value reported by any of the various ERROR_xxx functions to decide what to do with the control flow of your procedure and whether to abort any transactions. In our example in Listing 2-35, which attempts to divide by zero, here are the values returned by the error functions. (The ERROR_PROCEDURE function returns NULL in this example because the exception did not occur within a stored procedure.) ErrNumber ErrSeverity ErrState ErrProc ErrLine ErrMessage ---------- ------------ ---------- -------- -------- ---------------------------------- 8134 16 1 NULL 2 Divide by zero error encountered. When you experience a transaction abort error inside a transaction located in the TRY block, control is passed to the CATCH block. The transaction then enters a failed state in which locks are not released and persisted work is not reversed until you explicitly issue a ROLLBACK statement. You're not allowed to initiate any activity that requires opening an implicit or explicit transaction until you issue a ROLLBACK. Certain types of errors are not detected by the TRY/CATCH block, and you end up with an unhandled exception even though the error occurred inside your TRY block. If this happens, the CATCH block is not executed. This is because CATCH blocks are invoked by errors that take place in actual executing code, not by compile or syntax errors. Two examples of such errors are syntax errors and statement-level recompile errors (for example, selecting from a nonexistent table). These errors are not caught at the same execution level as the TRY block, but at the lower level of execution—when you execute dynamic SQL or when you call a stored procedure from the TRY block. For example, if you have a syntax error inside a TRY block, you get a compile error and your CATCH block will not run, as shown here: -- Syntax error doesn't get caught BEGIN TRY SELECT * * FROM Customer END TRY BEGIN CATCH PRINT 'Error' END CATCH The result is an error from SQL Server, not from your CATCH block, as follows: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '*'. Statement-level recompilation errors also don't get caught by CATCH blocks. For example, using a nonexistent object in a SELECT statement in the TRY block forces an error from SQL Server, but your CATCH block will not execute, as shown here: -- Statement level recompilation doesn't get caught BEGIN TRY SELECT * FROM NonExistentTable END TRY BEGIN CATCH PRINT 'Error' END CATCH The result is an error from SQL Server, as follows: Msg 208, Level 16, State 1, Line 3 Invalid object name 'NonExistentTable'. When you use dynamic SQL or a stored procedure, these types of compile errors do get caught because they are part of the current level of execution. Each of the SQL blocks shown in Listing 2-36 will execute the CATCH block. Listing 2-36. Catching syntax and recompilation errors in dynamic SQL and stored procedure calls with exception handlers -- Dynamic SQL Example BEGIN TRY EXEC sp_executesql 'SELECT * * FROM Customer' END TRY BEGIN CATCH PRINT 'Error' END CATCH GO -- Stored Procedure Example CREATE PROCEDURE MyErrorProc AS SELECT * FROM NonExistentTable GO BEGIN TRY EXEC MyErrorProc END TRY BEGIN CATCH PRINT 'Error' END CATCH