TempDB 中表变量和局部临时表的对比

SQLServer走起 2020-07-29

795

我们都知道，tempdb是用来为应用程序和SQL Server临时储存运行的中间结果的。

由用户和应用程序创建的对象叫做用户对象，由SQLServer引擎产生的对象叫做内部对象，在这篇博文中，我们主要讨论用户对象中的临时表（#，##）和表变量。大家可能对##表（全局临时表）和#表（局部临时表）的区别比较了解，但对临时表和表变量却不是很清楚，下面我们详述两者的主要区别。

和其他变量一样，表变量是一种非常有用的程序构造。表变量的有效范围和其他程序变量的有效范围是一样的。例如，如果你在存储过程中定义了一个变量，那么它就不能在存储过程外被访问。巧合的是，临时表也是这样的。那为什么我们还要创建表变量呢？因为表变量在存储过程中可以作为输出/输入参数（此功能从SQLServer2008开始可用）或者用来存储函数的返回结果。

以下是表变量和临时表的相同和不同之处：

• 首先，表变量不一定常驻内存。在内存压力大的时候，属于表变量的页可以被放入tempdb。以下是一个例子描述表变量在tempdb中所占空间。

USE tempdb;
GO

DROP TABLE #tv_source;
GO

CREATE TABLE #tv_source
    (
        c1 INT ,
        c2 CHAR(8000)
    );
GO

DECLARE @i INT;
SELECT @i = 0;
WHILE ( @i < 1000 )
    BEGIN
        INSERT INTO #tv_source
        VALUES ( @i, REPLICATE('a', 100));
        SELECT @i = @i + 1;
    END;

DECLARE @tv_target TABLE
    (
        c11 INT ,
        c22 CHAR(8000)
    );


INSERT INTO @tv_target ( c11 ,
                         c22 )
            SELECT c1 ,
                   c2
            FROM   #tv_source;

-- checking the size through DMV.
-- The sizes here are in 8k pages. This shows the allocated space
-- to user objects to be 2000 pages (1000 pages for #tv_source AND  1000 pages for @tv_target

SELECT total_size = SUM(unallocated_extent_page_count)
                    + SUM(user_object_reserved_page_count)
                    + SUM(internal_object_reserved_page_count)
                    + SUM(version_store_reserved_page_count)
                    + SUM(mixed_extent_page_count) ,
       SUM(unallocated_extent_page_count) AS freespace_pgs ,
       SUM(user_object_reserved_page_count) AS user_obj_pgs ,
       SUM(internal_object_reserved_page_count) AS internal_obj_pgs ,
       SUM(version_store_reserved_page_count) AS version_store_pgs ,
       SUM(mixed_extent_page_count) AS mixed_extent_pgs
FROM   sys.dm_db_file_space_usage;

• 其次，如果您创建了一个表变量，它会像一个常规的DDL操作一样将元数据储存在系统目录中，以下示例说明了这一点：

DECLARE @ttt TABLE
    (
        c111 INT ,
        c222 INT
    );
SELECT name
FROM   sys.columns
WHERE  object_id > 100
       AND name LIKE 'c%';

结果会返回两行，包含列C111和C222。这表明如果遇到定义冲突时，把临时表改成表变量不能解决问题。

• 第三，事务处理和锁定语句。表变量不能参与事务处理和锁定，以下示例说明了这一点

-- create a source table
CREATE TABLE tv_source
    (
        c1 INT ,
        c2 CHAR(100)
    );
GO

DECLARE @i INT;
SELECT @i = 0;
WHILE ( @i < 100 )
    BEGIN
        INSERT INTO tv_source
        VALUES ( @i, REPLICATE('a', 100));
        SELECT @i = @i + 1;
    END;
-- using #table
CREATE TABLE #tv_target
    (
        c11 INT ,
        c22 CHAR(100)
    );
GO

BEGIN TRAN;

INSERT INTO #tv_target ( c11 ,
                         c22 )
            SELECT c1 ,
                   c2
            FROM   tv_source;


--using table variable

DECLARE @tv_target TABLE
    (
        c11 INT ,
        c22 CHAR(100)
    );

BEGIN TRAN;
INSERT INTO @tv_target ( c11 ,
                         c22 )
            SELECT c1 ,
                   c2
            FROM   tv_source;


-- Now if I look at the locks, you will see that only
-- #table takes locks. Here is the query that used
-- to check the locks   
SELECT t1.request_session_id AS spid ,
       t1.resource_type AS type ,
       t1.resource_database_id AS dbid ,
       ( CASE resource_type
              WHEN 'OBJECT' THEN
                  OBJECT_NAME(t1.resource_associated_entity_id)
              WHEN 'DATABASE' THEN ' '
              ELSE (   SELECT OBJECT_NAME(object_id)
                       FROM   sys.partitions
                       WHERE  hobt_id = resource_associated_entity_id )
         END ) AS objname ,
       t1.resource_description AS description ,
       t1.request_mode AS mode ,
       t1.request_status AS status ,
       t2.blocking_session_id
FROM   sys.dm_tran_locks AS t1
       LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address;

另一个有趣的现象是，如果回滚的事务里涉及表变量，表变量的数据不会被回滚。表变量不参与事务

ROLLBACK  
-- this query will return 100 for table variable but 0 for #table.
SELECT COUNT(*) FROM @tv_target

• 第四，表变量上的操作不被ldf日志文件记录。请看下面这个例子：

--create a table variable, insert bunch of rows and update
DECLARE @tv_target TABLE
    (
        c11 INT ,
        c22 CHAR(100)
    );

INSERT INTO @tv_target ( c11 ,
                         c22 )
            SELECT c1 ,
                   c2
            FROM   tv_source;


-- update all the rows
UPDATE @tv_target
SET    c22 = REPLICATE('b', 100);


-- look at the top 10 log records. I get no records for this case
SELECT   TOP 10 Operation ,
         Context ,
         [Log Record Fixed Length] ,
         [Log Record Length] ,
         AllocUnitId ,
         AllocUnitName
FROM     fn_dblog(NULL, NULL)
WHERE    AllocUnitName LIKE '%tv_target%'
ORDER BY [Log Record Length] DESC;

-- create a local temptable
DROP TABLE #tv_target;
GO

CREATE TABLE #tv_target
    (
        c11 INT ,
        c22 CHAR(100)
    );
GO


INSERT INTO #tv_target ( c11 ,
                         c22 )
            SELECT c1 ,
                   c2
            FROM   tv_source;


--update all the rows
UPDATE #tv_target
SET    c22 = REPLICATE('b', 100);


-- look at the log records. Here I get 100 log records for update
SELECT   Operation ,
         Context ,
         [log
record fixed length]   ,
         [Log Record Length] ,
         AllocUnitName
FROM     fn_dblog(NULL, NULL)
WHERE    AllocUnitName LIKE '%tv_target%'
ORDER BY [Log
Record Length] DESC;

• 第五，表变量中不允许DDL语句，所以，如果你有一个大的行集需要经常进行查询，您可能要使用临时表并创建合适的索引。你可以在声明表变量时创建唯一约束来解决这个问题。

• 第六，表变量不维护统计信息数据。这意味着任何表变量数据更改都不会引起相关查询语句进行重编译。

• 最后，涉及表变量的查询不能生成并行的查询计划，因此我们认为对于庞大的临时数据集最好使用临时表来发挥并行查询的优势。

总结：表变量比临时表更轻量级，能做的事情比临时表少一点，但是不能判断表变量比临时表差，主要看应用场景

文章转载自：

http://blogs.msdn.com/b/apgcdsd/archive/2012/03/27/tempdb-compare.aspx

文章经作者授权转载，版权归原文作者所有

图片来源于网络，侵权必删！

数据库

文章转载自SQLServer走起，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

TempDB 中表变量和局部临时表的对比

评论