您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server Yukon (2005) T-SQL 新特性全景解析

編輯：關於SqlServer

SQL Server 2005（代號 Yukon）引入革命性的 T-SQL 增強，大幅提升可程式性、錯誤處理與查詢能力。本文深度解析 TRY/CATCH、SNAPSHOT 隔離、遞歸 CTE、PIVOT/UNPIVOT、APPLY 等核心新特性。

1. 結構化錯誤處理：TRY/CATCH

終結 @@ERROR 逐行檢查時代，支援交易中止錯誤捕捉（含死結、轉換錯誤）。

基本架構

BEGIN TRY
    BEGIN TRANSACTION
    -- 高風險操作
    INSERT INTO Accounts (Id, Balance) VALUES (1, -100) -- 觸發 CHECK 約束違規
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    -- 錯誤資訊收集
    SELECT
        ERROR_NUMBER() AS ErrNo,
        ERROR_SEVERITY() AS Severity,
        ERROR_STATE() AS State,
        ERROR_PROCEDURE() AS ProcName,
        ERROR_LINE() AS LineNo,
        ERROR_MESSAGE() AS MsgText
    -- 記錄錯誤日誌
    INSERT INTO ErrorLog (ErrNo, MsgText, LogTime) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH

關鍵行為

錯誤發生時交易進入 不可提交狀態，必須明確 ROLLBACK
XACT_ABORT ON 可將更多錯誤提升為可捕捉的交易中止錯誤
支援巢狀 TRY/CATCH，內層未處理會外層捕捉
RAISERROR(..., TRAN_ABORT) 可主動拋出可捕捉錯誤

2. SNAPSHOT 隔離等級：讀寫不阻塞

基於 tempdb 版本鏈，提供陳述式層級或交易層級的一致性讀取。

啟用與使用

-- 資料庫層級啟用
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON -- 陳述式層級

-- 會話層級指定
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
    SELECT * FROM Accounts WHERE Id = 1 -- 讀取提交版本，不被寫入阻塞
    -- ... 業務邏輯 ...
    UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1 -- 若版本衝突拋錯 3960
COMMIT TRAN

衝突處理模式

-- 樂觀鎖定重試邏輯
DECLARE @retry INT = 3
WHILE @retry > 0
BEGIN
    BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT
        BEGIN TRAN
            -- 業務操作
            COMMIT TRAN
        BREAK
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 3960 -- 更新衝突
            SET @retry = @retry - 1
        ELSE
            THROW
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
    END CATCH
END

3. 共用資料表運算式 (CTE) 與遞歸查詢

非遞歸 CTE：簡化複雜查詢

WITH TopCustomers AS (
    SELECT CustomerId, SUM(TotalDue) AS TotalSales
    FROM SalesOrderHeader
    WHERE OrderDate >= '2004-01-01'
    GROUP BY CustomerId
)
SELECT c.CustomerId, c.AccountNumber, tc.TotalSales
FROM Customer c
JOIN TopCustomers tc ON c.CustomerId = tc.CustomerId
WHERE tc.TotalSales > 100000

遞歸 CTE：階層資料查詢

WITH OrgChart AS (
    -- Anchor member
    SELECT EmployeeId, ManagerId, Title, 0 AS Level
    FROM Employees WHERE ManagerId IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.EmployeeId, e.ManagerId, e.Title, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerId = oc.EmployeeId
)
SELECT * FROM OrgChart ORDER BY Level, EmployeeId

注意：預設最大遞歸 100 層，可用 OPTION (MAXRECURSION 0) 移除限制。

4. PIVOT / UNPIVOT：行列轉換

PIVOT：列轉行

SELECT VendorId, [2003] AS Sales2003, [2004] AS Sales2004
FROM (
    SELECT VendorId, YEAR(OrderDate) AS OrderYear, SubTotal
    FROM PurchaseOrderHeader
) AS SourceTable
PIVOT (
    SUM(SubTotal) FOR OrderYear IN ([2003], [2004])
) AS PivotTable

UNPIVOT：行轉列

SELECT ProductId, Year, Sales
FROM (
    SELECT ProductId, [2003], [2004]
    FROM ProductSales
) AS p
UNPIVOT (
    Sales FOR Year IN ([2003], [2004])
) AS unpvt

5. APPLY 運算子：表值函數側向連接

CROSS APPLY：內連接語義

-- 取得每個客戶最近 3 筆訂單
SELECT c.CustomerId, o.OrderId, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderId, OrderDate
    FROM SalesOrderHeader
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) o

OUTER APPLY：左外連接語義

-- 無訂單客戶也會列出
SELECT c.CustomerId, o.OrderId, o.OrderDate
FROM Customers c
OUTER APPLY (
    SELECT TOP 3 OrderId, OrderDate
    FROM SalesOrderHeader
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) o