Sqlserver 关于临时表和表变量的总结

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#temporary-tables
https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-ver15

结论
1、一旦创建本地临时表的会话退出,本地临时表将被自动删除;当创建全局临时表的会话结束时,并且最后一条引用此表的Transact-SQL语句完成后,全局临时表将被自动删除
2、本地临时表只对当前会话可见,全局临时表的表结构和表里的数据同时对所有会话可见,其他会话可以查询和修改全局临时表
3、不管本地临时表还是全局临时表,就算指定了数据库,都是创建创建到了tempdb数据库下
4、表变量的作用域就是可以引用该变量的 Transact-SQL 语句的范围。 表变量的作用域从声明变量的地方开始到声明变量的批处理或存储过程的结尾,一个表变量声明后使用GO再查询该表变量,则查询报错,因为在GO后没有在再声明表变量,所以表变量自身没有锁的概念,就像oracle的PGA,自己私有的东西没有共享概念就没有锁
5、实际工作中遇到2起案例当表变量涉及的sql引发了sql很慢的情况,当把表变量修改为临时表后,情况好了很多,微软官方文档的解释:Table变量没有分发统计信息。 它们不会触发重新编译。 在 SQL Server 优化器基于成本的原因模型中,不支持 table 变量。 因此,在需要基于成本的选择来实现高效的查询计划时,不应使用这些变量。 在需要基于成本的选择时,临时表是首要选项。 出于这一原因,如果您预计会存在大量行(超过 100 行),那么在使用 table 变量时应小心谨慎。 请考虑以下替代方法:
当行计数可能更大(超过 100 行)时,临时表可能是比表变量更好的解决方案。
如果查询联接 table 变量和其他表,则可使用 RECOMPILE 提示,这使优化器会对 table 变量使用正确的基数。

实验1
会话1

use testdb1
CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有结果
select * from #localtemp2;--有结果
select * from ##globaltemp1;--有结果
select * from ##globaltemp2;--有结果

备注:发现表并不是建立在testdb1库下面,而是建立在tempdb这个库下面
会话2

select * from #localtemp1;--报错,表不存在
select * from #localtemp2;--报错,表不存在
select * from ##globaltemp1;--有结果
select * from ##globaltemp2;--有结果

会话1退出
会话2

select * from #localtemp1;--报错,表不存在
select * from #localtemp2;--报错,表不存在
select * from ##globaltemp1;--报错,表不存在
select * from ##globaltemp2;--报错,表不存在

结论:创建本地临时表的会话一旦退出,本地临时表自动销毁了,创建全局临时表的会话一旦退出,并且没有会话引用该全局临时表,则该全局临时表也自动销毁了

实验2
会话1

CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有结果,1行数据
select * from #localtemp2;--有结果,1行数据
select * from ##globaltemp1;--有结果,1行数据
select * from ##globaltemp2;--有结果,1行数据

会话2

CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));--报错,表已经存在
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));--报错,表已经存在

会话2

CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有结果,1行数据
select * from #localtemp2;--有结果,1行数据
select * from ##globaltemp1;--有结果,2行数据
select * from ##globaltemp2;--有结果,2行数据

结论2:本地临时表只对当前会话可见,全局临时表的表结构和表里的数据同时对所有会话可见

实验3

CREATE TABLE testdb1.dbo.#localtemp12(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
Database name 'testdb1' ignored, referencing object in tempdb.
CREATE TABLE testdb1.dbo.##localtemp13(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
Database name 'testdb1' ignored, referencing object in tempdb.

结论3:不管本地临时表还是全局临时表,就算指定了数据库,都是创建创建到了tempdb数据库下

实验4.1

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1]
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1]
select * from  @LOCALTB1 --有结果
select * from  @@GLOBALTB1--有结果

实验4.2

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1]
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1]
GO
select * from  @LOCALTB1 --报错Must declare the table variable "@LOCALTB1".
select * from  @@GLOBALTB1 --报错Must declare the table variable "@@GLOBALTB1".

实验4.3

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
GO
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1] --报错Must declare the table variable "@LOCALTB1".
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1] --报错Must declare the table variable "@@GLOBALTB1".
GO
select * from  @LOCALTB1 --报错Must declare the table variable "@LOCALTB1".
select * from  @@GLOBALTB1 --报错Must declare the table variable "@@GLOBALTB1".
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值