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表变量，与临时表相比，它提供了一些性能优势和灵活性。