Problem solve Get help with specific problems with your technologies, process and projects.

What's new with T-SQL in SQL Server 2005

When asked what the most valuable new features are in the soon-to-be-released SQL Server 2005, users commonly refer to its T-SQL enhancements. Edgewood Solutions' Jeremy Kadlec explains the significant enhancements in error handling, transferring rows to columns and XML.

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

This was last published in October 2005

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close