SQL Server，使用TRUNCATE的临时表 VS 使用DELETE的表变量

Question

我有一个存储过程，在其中我创建了一个临时表，通常包含1到10行。在存储过程中，这个表被清空和填充多次。由于速度更快，它被截断而不是删除。

如果我使用表变量替换这个临时表，是否会获得性能提升，但要承受使用删除的惩罚（截断对表变量不起作用）？虽然表变量主要在内存中，通常比临时表快，但由于必须使用删除而不是截断，我是否会失去任何好处？

- Richard

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   

- Adriaan Stander

---

更准确的说法是使用 DELETE 是更好的选择，而且与使用表变量相比，在使用 #temp 表时你会得到同样的结果。这是一个非常特殊的情况，因为这10行数据都可以放在一页中。TRUNCATE 会从表中回收最后一页的空间，而 DELETE 则不会。由于表非常小，因此记录被删除行所需的开销小于持续回收和重新分配表中单个页面带来的开销。但对于大型表格，情况则不同。 - Martin Smith

那么......对于一个有1000行的大表，截断会更好吗？相比逐行删除数据，截断通常要快得多，因为它会不加区分地清除所有数据，并且不像逐行删除那样需要写入大量日志。 - Triynko

Answer 2

坦白地说，只有10或20（甚至100）个条目，速度上的任何差异都将在亚纳秒级别。不要考虑它-甚至不要浪费一秒钟的大脑时间在这个问题上-这是一个无关紧要的问题！

总的来说：

表变量会被保留在内存中，如果超出了某个大小，则会像临时表一样在tempdb数据库中交换到磁盘上。此外：如果临时表只有少数几个条目，则它们很可能会存储在单个8k页面上，一旦您访问其中一个条目，整个页面（因此整个临时表）都将在SQL Server内存中，所以即使在这里，使用表变量也没有太多好处...

表变量不支持索引和统计信息，这意味着如果您有多个条目，尤其是如果需要搜索和查询此“实体”，则最好使用临时表。

总而言之：我个人更经常使用临时表而不是表变量，特别是如果我有超过10个条目之类的东西。能够对临时表进行索引，并在其上拥有统计信息，通常比表变量可能具有的任何潜在收益（性能方面）带来更大的回报。

- marc_s

---

谢谢，我同意删除和截断之间的差异可能是微不足道的。这实际上是一个学术问题，因为在我的过程中将临时表替换为表变量使得差异约为10%。正如“Spender”所说，我刚刚测试了一下。 - Richard

我自己也在考虑和OP类似的情况。临时表可以是10、25、250行，这取决于数据的设置方式。在我的测试场景中，我比较了一个存储过程中实际执行计划，该存储过程具有一个迭代的临时表，然后在外部循环中再次重用。我们不想有旧数据，所以我们使用DELETE清除临时表。这在批处理的4%中显示出来（有很多复杂的操作）。如果我用TRUNCATE替换它，它根本不会出现在计划中！这是TRUNCATE的正常行为吗（不会显示在执行计划中），还是它太微不足道，与计划无关？ - Jeff Mergler