SQL Server，带有截断的临时表与带有删除的表变量

Question

我有一个存储过程，在其中创建一个通常包含 1 到 10 行的临时表。该表在存储过程期间被截断和填充多次。它被截断，因为这比删除更快。 当我因使用删除而受到惩罚（截断不适用于表变量）时，通过用表变量替换此临时表是否会提高性能

虽然表变量主要在内存中并且通常比临时表更快，但我是否会丢失任何数据通过删除而不是截断来受益？

Answer 1

运行以下脚本，似乎表变量是

CREATE TABLE #Temp(
        ID INT
)

DECLARE @Int INT,
        @InnerInt INT
SELECT  @Int = 1,
        @InnerInt = 1

WHILE @Int < 50000
BEGIN
    WHILE @InnerInt < 10
    BEGIN
        INSERT INTO #Temp SELECT @InnerInt
        SET @InnerInt = @InnerInt + 1
    END
    SELECT @Int = @Int + 1,
            @InnerInt = 1
    TRUNCATE TABLE #Temp
END

DROP TABLE #TEMP

GO

DECLARE @Temp TABLE(
        ID INT
)

DECLARE @Int INT,
        @InnerInt INT
SELECT  @Int = 1,
        @InnerInt = 1

WHILE @Int < 50000
BEGIN
    WHILE @InnerInt < 10
    BEGIN
        INSERT INTO @Temp SELECT @InnerInt
        SET @InnerInt = @InnerInt + 1
    END
    SELECT @Int = @Int + 1,
            @InnerInt = 1
    DELETE FROM @Temp
END

来自 Sql Profiler 的更好选项

CPU     Reads   Writes  Duration
36375     2799937   0       39319

vs

CPU     Reads   Writes  Duration
14750   1700031 2       17376   

Answer 2

坦率地说，只有 10 或 20（甚至 100）个条目，速度上的任何差异都将在亚纳秒范围内。算了——别在这上面浪费一秒钟的时间——这不是问题！

一般来说，

• 表变量将在内存中保留一定大小 - 如果超出该大小，它们也会被交换到 tempdb 数据库中的磁盘 - 就像临时表一样。另外：如果临时表只有少数条目，那么它们很可能存储在单个 8k 页面上，并且一旦您访问其中一个条目，整个页面（以及整个临时表）就会位于 SQL Server 内存中 - 所以即使在这里，表变量实际上也没有太多好处...

• 表变量不支持索引或统计信息，这意味着如果您有多个条目，并且特别是如果您需要搜索和查询这个“实体”，您最好使用临时表

所以总而言之：我个人使用临时表比表变量更频繁，特别是如果我有超过 10 个条目或类似的东西。在性能方面，与表变量可能具有的任何潜在收益相比，能够对临时表进行索引并对其进行统计通常会带来很大的回报。