CTE，临时表和表变量

Question

请问在什么情况下应该使用CTE、临时表和表变量？

我已经了解它们之间的区别，但是在使用上还是有些困惑，请帮忙解答。

谢谢！

- SQLnewbe

Answer 1

您可以使用公用表表达式（CTE）来替代子查询或在需要递归的情况下使用。

CTE仅在包含它的SQL语句期间可用。前面和后面的语句将无法访问它并且不会看到它。它的行为类似于子查询，但可以在以下select/update中多次使用。

此查询使用两个子查询的子查询：

Select D.* From D
Inner Join (
        Select id value, date From A
        Inner Join B on A.data < B.date
        Inner Join C on C.data > B.date
    ) CTE a c1 on c1.id = D.id+1
Inner Join (
    Select id value, date From A
    Inner Join B on A.data < B.date
    Inner Join C on C.data > B.date
) as c2 on c2.id = D.id-1

可以使用CTE替换：

; with CTE as (
    Select id value, date From A
    Inner Join B on A.data < B.date
    Inner Join C on C.data > B.date
)
Select D.* From D
Inner Join CTE as c1 on c1.id = D.id+1
Inner Join CTE as c2 on c2.id = D.id-1

在这种情况下，这很有用，因为不需要多次编写相同的子查询。

递归CTE（这只是一个示例，SQL Server工作不应该像这样操作字符串数据）：

Declare @data varchar(50) = 'Recursive CTE' 
; With list(id, letter) as (
    Select 1, SUBSTRING(@data, 1, 1)
    Union All
    Select id+1, SUBSTRING(@data, id+1, 1) From list
    Where id < len(@data)
) 
Select * from list

递归CTE可用于检索层次结构数据。

表变量

表变量仅在查询执行时存在。在其创建后，它对所有SQL语句都可见。

当您需要使用表类型参数将数据传递给存储过程或函数时，可以使用它们：

Create Proc test(
    @id int,
    @list table_type_list READONLY
)
begin
    set nocount on
    select * from @list
end

Declare @t table_type_list
Insert into @t(name) values('a'), ('b'), ('c')
Exec test 1, @t

当您需要存储不太大且不需要索引的内容时，也可以使用它们。尽管在表声明中使用主键或唯一约束将自动创建索引，但您无法手动创建索引。

对于表变量，没有创建统计信息的选项，也无法创建统计信息。

临时表

当您处理更多数据并从创建索引和统计信息中受益时，可以使用临时表。

在会话中，任何语句一旦创建了表，就可以使用或更改该表：

create table #temp
Insert into #temp(...) select ... From data
exec procA
exec procB
exec procC

ProcA、ProcB和ProcC都可以从#temp中选择、插入、删除或更新数据。

表#temp将在用户会话关闭时立即被删除。

如果您想在会话之间保留临时表，则可以使用全局临时表（##temp）。它将一直存在，直到被删除或服务器重新启动。

- Julien Vavasseur