BI-SQL丨表变量与临时表

表变量与临时表

白茶在之前描述WITH AS的用法时，曾提到过，除了WITH AS以外，我们还有其他两种办法来进行类似DAX中VAR定义虚拟表的行为，本期我们来了解一下：如何使用表变量与临时表。

表变量

特征：

1.表变量拥有特定的作用域且在批处理结束以后会被自动的清除；

2.表变量相较于临时表，会产生更少的存储过程重编译；

3.表变量的事务行为仅在更新数据时生效，因此锁和日志产生的数量较少；

4.表变量的作用域较小，不属于数据库持久的部分，因此事务的回滚不影响表变量。

表变量可以像正常的物理表一样使用，我们可以对其进行增删改查，但是在特定的语句中，我们需要注意。

注意事项：

1.表变量不能赋值给另一个变量；

2.Check约束、默认值和计算列不能引用自定义函数；

3.不能为约束命名；

4.不能使用Truncate命令清除表变量数据保留表结构；

5.不能向标识列中插入显式值；

6.注意局部变量前缀为@，全局变量为@@。

语法：

单个变量：

DECLARE @变量名称 数据类型
SET @变量名称=值

表变量：

DECLARE @变量表名称 TABLE
(列名称 数据类型,
列名称 数据类型,
...
)
INSERT INTO @变量表名称 VALUES(值，值,...)

使用实例：

例子1：生成单个变量

DECLARE @BAICHA VARCHAR(255)
SET @BAICHA='白茶'
SELECT @BAICHA

结果如下：

例子2：生成表变量

DECLARE @BAICHA TABLE
(Customer VARCHAR(255),
Quantity INT,
Age INT
)
INSERT INTO @BAICHA VALUES('白茶',10000,30)
SELECT * FROM @BAICHA

结果如下：

临时表

临时表与物理表的区别：

1.临时表的名称不能超过116字符；

2.局部临时表以"#"开头命名，作用域仅仅在当前连接内，会话结束后，会被自动删除，不可以被其他连接调用；

3.全局临时表以"##"开头命名，创建时对任何数据库连接都是可见的，当所有连接断开时，会自动进行删除；

4.不能对临时表进行分区；

5.不能对临时表加外键约束。

语法：

CREATE TABLE #临时表名称(或##临时表名称)
(列名称 数据类型,
列名称 数据类型,
...
)
INSERT INTO #(或##)临时表名称 VALUES(值，值,...)

使用实例：

创建一个临时表。

CREATE TABLE #BAICHA
(Customer VARCHAR(255),
Quantity INT,
Age INT
)
INSERT INTO #BAICHA VALUES('白茶',10000,30)
SELECT * FROM #BAICHA

结果如下：

认知误区：

Q：表变量仅仅存储在内存中，而临时表存储在物理介质中。
A：这两种观点都是错误的，只要内存足够，二者都会在内存中创建处理。也同样可以转入物理介质。
Q：表变量不能拥有索引。
A：这个也是错误的，当表变量创建后，对其添加索引的操作是不可以的，但是可以在定义表变量的时候直接定义索引。

最后是关于表变量和临时表的选择问题，严格来说我们在开发过程中并没有严格要求必须选择哪一种，这个可能需要小伙伴在使用过程中咨询相应的开发规范，如果没有的话，可酌情选择。

这里是白茶，一个PowerBI的初学者。