SQL Server表變數

在本教學中，將瞭解保存數據行的SQL Server表變數。

表變數是什麼

表變數是一種用於保存數據行的變數，類似於臨時表。

如何聲明表變數

要聲明表變數，請使用DECLARE語句，如下所示：

DECLARE @table_variable_name TABLE (
    column_list
);

在此語法中，指定DECLARE和TABLE關鍵字之間的表變數的名稱。表變數的名稱必須以井號(#)開頭。

在TABLE關鍵字之後，定義表變數的結構，該結構類似於包含列定義，數據類型，大小，可選約束等的常規表的結構。

表變數的範圍

與局部變數類似，表變數在批處理結束時超出範圍。

如果在存儲過程或用戶定義函數中定義表變數，則在存儲過程或用戶定義的函數退出後，表變數將不再存在。

表變數示例

例如，以下語句聲明了一個名為@product_table的表變數，該變數由三列組成：product_name，brand_id和list_price：

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

將數據插入表變數
聲明後，表變數為空。可以使用INSERT語句將行插入表變數：

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

查詢表變數中的數據

與臨時表類似，可以使用SELECT語句從表變數中查詢數據：

SELECT
    *
FROM
    @product_table;

請注意，需要執行一整批次，否則將收到錯誤消息：

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

SELECT
    *
FROM
    @product_table;
GO

執行上面示例代碼，得到以下結果：

表變數的限制

首先，必須在聲明期間定義表變數的結構。 與常規表或臨時表不同，無法在聲明後更改表變數的結構。

其次，統計資訊可以幫助查詢優化器提出一個好的查詢執行計畫。不幸的是，表變數不包含統計資訊。 因此，應該使用表變數來保存少量行。

第三，不能像其他數據類型一樣將表變數用作輸入或輸出參數。 但是，可以從用戶定義的函數返回表變數。

第四，不能為表變數創建非聚集索引。 但是，從SQL Server 2014開始，記憶體優化的表變數可用於引入新的記憶體中OLTP，允許添加非聚簇索引作為表變數聲明的一部分。

第五，如果使用帶有連接的表變數，則需要對表進行別名以執行查詢。 例如：

SELECT
    brand_name,
    product_name,
    list_price
FROM
    brands b
INNER JOIN @product_table pt ON p.brand_id = pt.brand_id;

表變數的性能

與使用臨時表相比，在存儲過程中使用表變數可以減少重新編譯。此外，表變數使用的資源少於具有較少鎖定和日誌記錄開銷的臨時表。
與臨時表類似，表變數確實存在於tempdb資料庫中，而不是存儲在內存中。

在用戶定義的函數中使用表變數

以下用戶定義的函數名為ufnSplit()，它返回一個表變數。

CREATE OR ALTER FUNCTION udfSplit(
    @string VARCHAR(MAX),
    @delimiter VARCHAR(50) = ' ')
RETURNS @parts TABLE
(
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)
)
AS
BEGIN

DECLARE @index INT = -1;

WHILE (LEN(@string) > 0)
BEGIN
    SET @index = CHARINDEX(@delimiter , @string)  ;

    IF (@index = 0) AND (LEN(@string) > 0)
    BEGIN
        INSERT INTO @parts
        VALUES (@string);
        BREAK
    END

    IF (@index > 1)
    BEGIN
        INSERT INTO @parts
        VALUES (LEFT(@string, @index - 1));

        SET @string = RIGHT(@string, (LEN(@string) - @index));
    END
    ELSE
    SET @string = RIGHT(@string, (LEN(@string) - @index));
    END
RETURN
END
GO

在以下語句中調用udfSplit()函數：

SELECT
    *
FROM
    udfSplit('foo,bar,baz',',');

執行上面查詢語句，得到以下結果：

在本教學中，將學習如何使用SQL Server表變數，與臨時表相比，它提供了一些性能優勢和靈活性。