SQL Server2005中T-SQL新特性

SQL Server 2005 中Transact-SQL编程增强的特性包括

• 错误处理: TRY and CATCH
• Transferring rows to columns: PIVOT and UNPIVOT
• XML 增强

Tip 1: Error handling with TRY and CATCH

错误处理是T-SQL较其他语言的一个劣势的部分，SQL Server 2005中TRY和CATCH和.NET语言中大多数语法类似，这方面的改进将大大提高SQL Server错误处理的能力。

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;

Tip 2: Transferring rows to columns with PIVOT and UNPIVOT

SQL Server 2005中PIVOT and UNPIVOT命令将行和列的转换变得简单。

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

Tip 3: XML enhancements

XML技术已经成为许多微软的应用在异构环境之间数据传输的通用途径，SQL Server 2005的XML改善了固有的创建、储存、传输和查询数据的能力.，SQL Server2005中实现以下功能：

• 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