SQL Server 表变量和临时表的区别(详细补充篇)

1. 介绍

在 SQL Server 中，有两种用于存储和处理临时数据的方式，那就是表变量和临时表。本文将会详细介绍这两种方式的区别，以及它们各自适用的场景。

2. 定义

2.1 表变量

表变量可以看作是一种特殊的变量，它以表的形式存储于内存中，可以在存储过程、函数或批处理中使用。

DECLARE @tableVariable TABLE (

    column1 datatype1 [ NULL | NOT NULL ],
    column2 datatype2 [ NULL | NOT NULL ],
    ...
);

注意：定义表变量的语法与定义普通变量的语法相似，只不过需要在表变量名之前添加 @。

2.2 临时表

SQL Server 中的临时表分为两种，分别是局部临时表和全局临时表。

局部临时表：局部临时表只在创建它的会话中存在，并且在会话结束时会自动删除。

全局临时表：全局临时表跨会话存在，直到创建它的最后一个会话结束时才会被删除。

临时表的命名规则与普通表相同，只不过以 # 开头的表示局部临时表，以 ## 开头的表示全局临时表。

CREATE TABLE #localTemporaryTable (

    column1 datatype1 [ NULL | NOT NULL ],
    column2 datatype2 [ NULL | NOT NULL ],
    ...
);

CREATE TABLE ##globalTemporaryTable (

    column1 datatype1 [ NULL | NOT NULL ],
    column2 datatype2 [ NULL | NOT NULL ],
    ...
);

3. 区别

3.1 存储位置

表变量存储在内存中，而临时表存储在 tempdb 数据库中。

3.2 作用范围

表变量只在定义它的块中存在。块可以是函数内、存储过程内或批处理中。

临时表的作用范围与常规表相同。局部临时表只在创建它的会话中存在，全局临时表跨会话存在。

3.3 索引和约束

表变量不支持索引、主键以及外键约束。

临时表可以像普通表一样定义索引和约束。

3.4 数据量限制

对于表变量，实际上由于它存储在内存中，因此存储数据量有一定的限制。

而对于临时表来说，数据量没有实际限制。

3.5 存储方式

表变量存储在内存中，而临时表存储在磁盘上，会增加 I/O 操作。

3.6 查询效率

在小数据量的情况下，表变量的查询效率要优于临时表。

但是在大数据量的情况下，临时表的查询效率要优于表变量。

4. 适用场景

4.1 表变量的适用场景

在使用少量数据时，例如在查询结果集比较小的情况下。

需要在函数、存储过程或批处理中使用，以避免与其他事务冲突。

需要多次查询同一个结果集，以避免多次读取磁盘。

4.2 临时表的适用场景

需要存储大量的数据时。

需要对数据进行复杂的操作，例如使用索引或约束。

需要在多个会话之间共享数据时，例如以临时表存储查询结果等。

5. 总结

表变量和临时表各有优劣，具体使用哪一种要因地制宜。对于小数据量、需要多次查询、在函数、存储过程或批处理中使用的情况，可以使用表变量；对于大数据量、需要进行复杂操作、需要共享数据的情况，可以使用临时表。