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,  AS Emp1,  AS Emp2,  AS Emp3,  AS Emp4,  AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( , , , ,  )) 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);
CREATE XML INDEX SXML_Order_OrderDetailsID
ON OrdersSchema.Orders (OrderDetailsID)
USING XML INDEX PXML_Orders_OrderID FOR PATH ;
- 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.
WHERE OrderID = 123