SQL Server表变量

要声明表变量，请使用如下语句：

DECLARE @table_variable_name TABLE (
    column_list
);

在此语法中，在 和 TABLE 关键字之间指定表变量的名称。 表变量名称必须以井号 (#) 开头。

在 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;

请注意，需要执行完整的批处理，否则您将收到错误消息：

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 2014 开始，内存优化表变量可用于引入新的内存中 OLTP，从而允许将非聚集索引添加为表变量声明的一部分。

第五，如果使用带有连接的表变量，则需要为表添加别名以执行查询。 例如：

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

表变量的性能

与使用临时表相比，在存储过程中使用表变量可以减少重新编译。 此外，表变量使用的资源比临时表更少，锁定和日志记录开销也更少。

与临时表一样，表变量确实存在于数据库中，而不是存储在内存中。

在用户定义函数中使用表变量

以下用户定义函数名为 ()，它返回一个表变量。

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

在以下语句中调用 () 函数：

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

执行上面的查询，得到如下结果：

在本教程中，了解如何使用 SQL 表变量，与临时表相比，它具有一些性能优势和灵活性。