SqlServer之表变量和临时表

时间：2022-01-31 08:19:18

表变量：

表变量创建的语法类似于临时表，区别就在于创建的时候，必须要为之命名。表变量是变量的一种，

表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，

一般都是系统的全局变量，像我们常用到的，如@@Error代表错误的号，@@RowCount代表影响的行数。

表变量有以下特点:

  • 表变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。

    在其作用域内，表变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是，表不能用在下列语句中：

    INSERT INTO table_variable EXEC 存储过程。

    SELECT select_list INTO table_variable 语句。
  • 在定义表变量的函数、存储过程或批处理结束时，自动清除表变量。
  • 表类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数。
  • 在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
  • 涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
  • 不支持在表变量之间进行赋值操作。

    declare @t1 table(t1 int)

    declare @t2 table(t2 int)

    set @t1=@t2 –错误
  • 另外，由于表变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。

示例：

DECLARE @News table 

　　( 

　　News_id int NOT NULL, 

　　NewsTitle varchar(100), 

　　NewsContent varchar(2000), 

　　NewsDateTime datetime 

　　) 

　　INSERT INTO @News (News_id, NewsTitle, NewsContent, NewsDateTime) 

　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 

　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM @News

临时表：

　临时表与永久表相似，只是它的创建是在Tempdb中，它只有在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志，虽它们在Tempdb中体现，是分配在内存中的，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。

　　临时表分为本地和全局两种，本地临时表的名称都是以“#”为前缀，只有在本地当前的用户连接中才是可见的，当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀，创建后对任何用户都是可见的，当所有引用该表的用户断开连接时被删除

示例：

CREATE TABLE dbo.#News

　　(

　　News_id int NOT NULL,

　　NewsTitle varchar(100),

　　NewsContent varchar(2000),

　　NewsDateTime datetime

　　)

　　INSERT INTO dbo.#News (News_id, NewsTitle, NewsContent, NewsDateTime)

　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())

　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM dbo.#News

　　DROP TABLE dbo.[#News]

用法：

表变量：无表关联操作，只作为中间集进行数据处理；临时表：有表关联，且不能确定数据量大小的情况下。理论如此，真正用法要按照实际项目需求而定。

特性 表变量 临时表
做哟作用域 当前批处理 当前回话，嵌套存储过程，全局：所有回话
使用场景 自定义函数，存储过程，批处理 自定义函数，存储过程，批处理
创建方式 DECLARE statement only,只能通过DECLEARE语句创建 CREATE TABLE语句，SELECT INTO语句
表名长度 最多128字节 最多116字节
列类型 可以使用自定义数据类型，可以使用XML集合 自定义数据类型和XML集合必须在TempDb内定义
Collation 字符串排序规则继承自当前数据库 字符串排序规则继承自TempDb数据库
索引 索引必须在表定义时建立 索引可以再表创建后建立
约束 PRIMARY KEY,UNIQUE,NULL,CHECK约束可以使用，但必须在表建立时声明 PRIMARY KEY,UNIQUE,NULL,CHECK约束可以使用，可以再任何时间添加，但没有外键约束
表建立后使用DDL (索引，列) 不允许 允许
数据插入方式 INSERT 语句 NSERT 语句, 包括 INSERT/EXEC.SELECT INTO 语句.
Insert explicit values into identity columns (SET IDENTITY_INSERT). 不支持SET IDENTITY_INSERT语句 支持SET IDENTITY_INSERT语句
Truncate table 不允许 允许
析构方式 批处理结束后自动析构 显式调用 DROP TABLE 语句. 当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)
事务 只会在更新表的时候有事务，持续时间比临时表短 正常的事务长度，比表变量长
存储过程重编译 否 会导致重编译
回滚 不会被回滚影响 会被回滚影响
统计数据 不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准 创建统计数据，通过实际的行数生成执行计划
作为参数传入存储过程 并且必须预定义 user-defined table type. 不允许
显式命名对象 (索引, 约束). 不允许 允许，但是要注意多用户的问题
动态SQL 必须在动态SQL中定义表变量 可以在调用动态SQL之前定义临时表

参考：

相关文章