New with T-SQL in SQL Server 2005

Three most important features in T-SQL are: 

  • Error handling: TRY and CATCH
  • Transferring rows to columns: PIVOT and UNPIVOT
  • XML enhancements

1. Error handling with TRY and CATCH

The TRY and CATCH paradigm is similar to many .NET languages.

BEGIN TRYSELECT 1/0;END TRY 

BEGIN CATCHSELECTERROR_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;

2. Transferring rows to columns with PIVOT and UNPIVOT

These commands enable the quick shifting of rows to columns and vice versa with few coding changes.

USE AdventureWorks;GOSELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )) AS pvtORDER BY VendorID

3. 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s