SQL Server 2008 表值參數 (TVP) 實戰應用：建立表類型、預存程序與代理鍵查找範例

TVP 核心優勢

• 可將表類型作為參數傳遞給函數/預存程序。
• 向 T-SQL 表變數匯入資料後整體傳遞，免除多參數或 XML 處理。
• 單次呼叫傳遞多行資料 (據稱可處理 1000 列)。

實作步驟 (CTP Feb 測試)

1. 建立表類型

CREATE TYPE SourceKeyList AS TABLE (SourceKey NVARCHAR(50));
-- 查詢現有表類型
SELECT name, system_type_id, user_type_id FROM sys.types WHERE is_table_type = 1;

2. 建立維度表與預存程序

CREATE TABLE dbo.dim_Customer (
    sk_Customer INT IDENTITY NOT NULL,
    CustomerSourceKey NVARCHAR(50) NOT NULL,
    CustomerName NVARCHAR(50) NOT NULL,
    InferredMember BIT NOT NULL
);

CREATE PROCEDURE dbo.stp_GetCustomerSK
    @source_key_list SourceKeyList READONLY
AS
BEGIN
    -- 插入推斷成員 (來源鍵不存在時)
    INSERT INTO dbo.dim_Customer (CustomerSourceKey, CustomerName, InferredMember)
    SELECT SourceKey, N'INFERRED', 1
    FROM @source_key_list k
    LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey
    WHERE sk_Customer IS NULL;

    -- 回傳代理鍵對應
    SELECT sk_Customer, CustomerSourceKey
    FROM dbo.dim_Customer c
    JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey;
END;

TVP 必須 READONLY，不可執行 DML，僅能於 SELECT 參考。可查詢 sys.parameters WHERE is_readonly = 1 驗證。

3. 宣告變數、填入資料、呼叫程序

DECLARE @source_key_list SourceKeyList;
INSERT INTO @source_key_list SELECT 'CustomerID_001' UNION ALL SELECT 'CustomerID_002' UNION ALL SELECT 'CustomerID_003';
EXEC dbo.stp_GetCustomerSK @source_key_list;

實際應用中可從來源系統 SELECT DISTINCT 取得鍵值清單。結果回傳每個來源鍵對應代理鍵。

資料倉庫應用場景

• 維度處理將來源鍵對應代理鍵，變更保留歷史 (新代理鍵新列)。
• 事實表載入時查找代理鍵，多事實表共用同維度查找邏輯，TVP 完美適用。
• 來源鍵不存在時建立推斷成員，後續以真實維度列取代更新。

結語

TVP 為資料倉庫 ETL 代理鍵查找提供高效、原生的集合傳遞機制，簡化開發並提升效能。