Introduction
In preparation for the SQL Server 2005 launch this November, I recently conducted a survey of Edgewood Solutions' customers, asking them what they consider to be the most valuable features in Microsoft's new database release. One common response across all positions, industries, company sizes and years in business was T-SQL enhancements. These findings serve as a basis for the following tips on Transact-SQL programming extensions.
- Error handling: TRY and CATCH
- Transferring rows to columns: PIVOT and UNPIVOT
- XML enhancements
Tip 1: Error handling with TRY and CATCH
Native error handling is the one area where T-SQL is often at fault compared to other languages. The TRY and CATCH paradigm, introduced in SQL Server 2005, is similar to many .NET languages. This will greatly improve the confidence in SQL Server's error handling with an approach that is familiar to many developers and DBAs.
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
Transferring rows to columns with PIVOT and UNPIVOT
A developer at a health care company that I work with growls like a dog every time her reporting users want to shift rows to columns. It is not a nice conversation. She has to write some tricky code that requires more than a few CPU cycles to give the users the data they need based on the state reporting requirements. These painful conversations are a thing of the past with the introduction of the PIVOT and UNPIVOT commands in SQL Server 2005. These commands enable the quick shifting of rows to columns and vice versa with few coding changes.
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
Source: Using PIVOT and UNPIVOT, SQL Server 2005 Books Online
XML enhancements
XML has become prevalent in transferring data across many heterogeneous environments and between many Microsoft applications; the SQL Server 2005 XML features improve the inherent capabilities to create, store, transport and query XML data. It is now possible to achieve the following natively in SQL Server:
- Create an XML schema that can be referenced in a table's column.
CREATE XML SCHEMA COLLECTION [ . ]sql_identifier AS Expression
- Create a native data type for table creation with pointers out to the XML Schema Collection, which are separate data pages from the base table, similar to a BLOB in SQL Server 2000.
CREATE TABLE Orders
(OrderID int PRIMARY KEY NOT NULL,
OrderDetailsID int NOT NULL,
OrderDate datetime NOT NULL,
…
XMLOrder xml NOT NULL)
- Create a variable as the XML data type for stored procedure or ad-hoc transactions.
DECLARE @OrdersSchema xml
…
CREATE XML SCHEMA COLLECTION OrdersSchema AS @OrdersSchema
- Improve the access to the XML data by creating primary and secondary indexes.
CREATE PRIMARY XML INDEX PXML_Orders_OrderID
ON OrdersSchema.Orders (OrderID);
GO
CREATE XML INDEX SXML_Order_OrderDetailsID
ON OrdersSchema.Orders (OrderDetailsID)
USING XML INDEX PXML_Orders_OrderID FOR PATH ;
GO
- Query XML data via a simple SELECT statement to return the XML as a portion of the result set with the remainder of the columns in the table.
SELECT *
FROM Orders
WHERE OrderID = 123
Conclusion
T-SQL takes a big step forward in SQL Server 2005 by expanding the capabilities of the traditional relational database management system. These items are just the tip of the iceberg with additional T-SQL improvements and overhauls to DTS -- now SQL Server Integration Services, Analysis Services, Reporting Services, etc. These are challenging, yet exciting, times to learn about all of the new capabilities SQL Server has to offer.
About the author:
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
More information from SearchSQLServer.com
Ask the Experts: Run a T-SQL query to loop through all databases
Fast Guide: Stored procedures
Topic: Research T-SQL best practices
