T-SQL之表变量和临时表
⼀、表变量
  表变量在SQL Server 2000中⾸次被引⼊。表变量的具体定义包括列定义，列名，数据类型和约束。⽽在表变量中可以使⽤的约束包括主键约束，唯⼀约束，NULL约束和CHECK约束(外键约束不能在表变量中使⽤)。定义表变量的语句是正常使⽤Create Table定义表语句的⼦集。只是表变量通过DECLARE @local_variable语句进⾏定义。
  表变量的特征：
1. 表变量拥有特定作⽤域(在当前批处理语句中，但不在任何当前批处理语句调⽤的存储过程和函数中)，表变量在批处理结束后⾃动被清
除。
2. 表变量较临时表产⽣更少的存储过程重编译。
3. 针对表变量的事务仅仅在更新数据时⽣效，所以锁和⽇志产⽣的数量会更少。
4. 由于表变量的作⽤域如此之⼩，⽽且不属于数据库的持久部分，所以事务回滚不会影响表变量。
  表变量可以在其作⽤域内像正常的表⼀样使⽤。更确切的说，表变量可以被当成正常的表或者表表达式⼀样在
SELECT,DELETE,UPDATE,INSERT语句中使⽤，但是表变量不能在类似"SELECT select_list INTO table_variable"这样的语句中使⽤。⽽在SQL Server2000中，表变量也不能⽤于INSERT INTO table_variable EXEC stored_procedure这样的语句中。
  表变量不能做如下事情:
1. 虽然表变量是⼀个变量，但是其不能赋值给另⼀个变量。
2. check约束，默认值和计算列不能引⽤⾃定义函数。
3. 不能为约束命名。
4. 不能Truncate表变量。
5. 不能向标识列中插⼊显式值(也就是说表变量不⽀持SET IDENTITY_INSERT ON)
  定义⼀个表变量，插⼊⼀条数据，然后查询：
DECLARE@tb1Table
  (
  Id int,
  Name varchar(20),
  Age int
  )
  INSERT INTO@tb1VALUES(1,'刘备',22)
  SELECT*FROM@tb1
  输出结果如下：
⼆、临时表
  会话(Session)，⼀个会话仅仅是⼀个客户端到数据引擎的连接。在SQL Server Management Studio中，
每⼀个查询窗⼝都会和数据库引擎建⽴连接。⼀个应⽤程序可以和数据库建⽴⼀个或多个连接，除此之外，应⽤程序还可能建⽴连接后⼀直不释放直到应⽤程序结束，也可能使⽤完释放连接需要时建⽴连接。
  临时表是建⽴在系统临时⽂件夹中的表，如果使⽤得当，完全可以像普通表⼀样进⾏各种操作。
临时表分为两类：本地临时表和全局临时表：
  [1] 本地临时表仅在当前会话中可见，本地临时表的名称前⾯有⼀个编号符 (#)；
  [2] 全局临时表在所有会话中都可见，全局临时表的名称前⾯有两个编号符 (##)。
如果本地临时表由存储过程创建或由多个⽤户同时执⾏的应⽤程序创建，则 SQL Server 必须能够区分由不同⽤户创建的表。为此，SQL Server 在内部为每个本地临时表的表名追加⼀个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由 Create Table 语句中指定的表名和系统⽣成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过 116 个字符。
除⾮使⽤ Drop Table语句显式除去临时表，否则临时表将在退出其作⽤域时由系统⾃动删除。
当存储过程完成时，将⾃动除去在存储过程中创建的本地临时表。由创建表的存储过程执⾏的所有嵌套存储过程都可以引⽤此表。但调⽤创建此表的存储过程的进程⽆法引⽤此表。
所有其它本地临时表在当前会话结束时⾃动删除。
全局临时表在创建此表的会话结束且其它任务停⽌对其引⽤时⾃动除去。任务与表之间的关联只在单个 Transact-SQL 语句的⽣存周期内保持。换⾔之，当创建全局临时表的会话结束时，最后⼀条引⽤此表的 Transact-SQL 语句完成后，将⾃动除去此表。
在存储过程或触发器中创建的本地临时表与在调⽤存储过程或触发器之前创建的同名临时表不同。如果查询引⽤临时表，⽽同时有两个同名的临时表，则不定义针对哪个表解析该查询。嵌套存储过程同样可以创建与调⽤它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引⽤都被解释为是针对该嵌套过程所创建的表。
当创建本地或全局临时表时，Create Table 语法⽀持除 Foreign Key 约束以外的其它所有约束定义。如果在临时表中指定 Foreign Key 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 Foreign Key约束。在Foreign Key约束中不能引⽤临时表。
考虑使⽤表变量⽽不使⽤临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使⽤表值时，临时表很有⽤。通常，表变量提供更有效的查询处理。
  1、创建临时表
-
-⽅法⼀：直接创建
create table #临时表名
(    字段1 约束条件,
字段2 约束条件,
.....)
create table ##临时表名
(    字段1 约束条件,
字段2 约束条件,
.....)
--⽅法⼆：插⼊特定数据创建
select*into #临时表名from表名;
select*into ##临时表名from表名;
  2、临时表中插⼊数据
Create Table #临时表名 (Number int Primary Key)
Insert Into #临时表名Values (1)
  3、查询临时表
select * from #临时表名;
select * from ##临时表名;
  4、删除临时表
drop table #临时表名;
drop table ##临时表名;
  5、清空临时表的所有数据和约束
truncate table #临时表名;
truncate table ##临时表名;
  临时表和Create Table语句创建的表有着相同的物理⼯程，但临时表与正常的表不同之处有：
  1、临时表的名称不能超过116个字符，这是由于数据库引擎为了辨别不同会话建⽴不同的临时表，所以会⾃动在临时表的名字后附加⼀串。
  2、局部临时表(以"#"开头命名的)作⽤域仅仅在当前的连接内，从在存储过程中建⽴局部临时表的⾓度来看，局部临时表会在下列情况下被Drop：
  [1] 显⽰调⽤Drop Table语句
  [2] 当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被Drop。
  [3] 当前会话结束，在会话内创建的所有局部临时表都会被Drop。
  3、全局临时表(以"##"开头命名的)在所有的会话内可见，所以在创建全局临时表之前⾸先检查其是否存在，否则如果已经存在，将会返回重复创建对象的错误。
  (1) 全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进⾏引⽤。
  (2) 引⽤是在语句级别进⾏，如:
  [1] 新建查询窗⼝，运⾏语句：
  CREATE TABLE ##temp(RowID int)
  INSERT INTO ##temp VALUES(3)
  [2] 再次新建⼀个查询窗⼝，每5秒引⽤⼀次全局临时表
  While 1=1
  BEGIN
  SELECT * FROM ##temp
  WAITFOR delay '00:00:05'
  END
  [3] 回到第⼀个窗⼝，关闭窗⼝。
  [4] 下⼀次第⼆个窗⼝引⽤时，将产⽣错误。
  4、不能对临时表进⾏分区。
  5、不能对临时表加外键约束。
  6、临时表内列的数据类型不能定义成在TempDb中没有定义的⾃定义数据类型(⾃定义数据类型是数据库级别的对象，⽽临时表属于TempDb)。由于TempDb在每次SQL Server重启后会被⾃动创建，所以你必须使⽤startup stored procedure来为TempDb创建⾃定义数据类型。你也可以通过修改Model数据库来达到这⼀⽬标。
  7、XML列不能定义成XML集合的形式，除⾮这个集合已经在TempDb中定义。
  临时表既可以通过Create Table语句创建，也可以通过"SELECT <select_list> INTO #table"语句创建。你还可以针对临时表⽤"INSERT INTO #table EXEC stored_procedure"这样的语句。
  临时表可以拥有命名的约束和索引。但是，当两个⽤户在同⼀时间调⽤同⼀存储过程时，将会产⽣”There is already an object named ‘<objectname>’ in the database”这样的错误。所以最好的做法是不⽤为建⽴的对象进⾏命名，⽽使⽤系统分配的在TempDb中唯⼀的。
三、误区
  1、表变量仅仅在内存中。
  2、临时表仅仅存储在物理介质中。
  以上两种观点都是错误的，只有内存⾜够，表变量和临时表都会在内存中创建和处理。也同样可以在任何时间被存⼊磁盘。
  注意表变量的名字是系统分配的，表变量的第⼀个字符”@”并不是⼀个字母，所以它并不是⼀个有效的变量名。系统会在TempDb中为表变量创建⼀个系统分配的名称，所以任何在sysobjects或sys.tables查表变量的⽅法都会失败。
  正确的⽅法应该是我前⾯例⼦中的⽅法，我看到很多⼈使⽤如下查询查表变量：
  select * from sysobjects where name like'#tempTables%'
  上述代码看上去貌似很好⽤，但会产⽣多⽤户的问题。你建⽴两个连接，在第⼀个连接中创建临时表，在第⼆个窗⼝中运⾏上⾯的语句能看到第⼀个连接创建的临时表，如果你在第⼆个连接中尝试操作这个临时表，那么可能会产⽣错误，因为这个临时表不属于你的会话。
  3、表变量不能拥有索引。
  这个误区也同样错误。虽然⼀旦你创建⼀个表变量之后，就不能对其进⾏DDL语句了，这包括Create Index语句。然⽽你可以在表变量定义的时候为其创建索引)⽐如如下语句。
  declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)
  这个语句将会创建⼀个拥有聚集索引的表变量。由于主键有了对应的聚集索引，所以⼀个系统命名的索引将会被创建在RowID列上。
下⾯的例⼦演⽰你可以在⼀个表变量的列上创建唯⼀约束以及如何建⽴复合索引。
  declare @temp TABLE (
  RowID int NOT NULL,
  ColA int NOT NULL,
  ColB char(1)UNIQUE,
  PRIMARY KEY CLUSTERED(RowID, ColA))
  1) SQL 并不能为表变量建⽴统计信息，就像其能为临时表建⽴统计信息⼀样。这意味着对于表变量，执⾏引擎认为其只有1⾏，这也意味着针对表变量的执⾏计划并不是最优。虽然估计的执⾏计划对于表变量和临时表都为1，但是实际的执⾏计划对于临时表会根据每次存储过程的重编译⽽改变。如果临时表不存在，在⽣成执⾏计划的时候会产⽣错误。
  2) ⼀旦建⽴表变量后就⽆法对其进⾏DDL语句操作。因此如果需要为表建⽴索引或者加⼀列，你需要临时表。
  3) 表变量不能使⽤select …into语句，⽽临时表可以。
  4) 在SQL Server 2008中，你可以将表变量作为参数传⼊存储过程。但是临时表不⾏。在SQL Server 2000和2005中表变量也不⾏。
  5) 作⽤域：表变量仅仅在当前的批处理中有效，并且对任何在其中嵌套的存储过程等不可见。局部临时表只在当前会话中有效，这也包括嵌套的存储过程。但对⽗存储过程不可见。全局临时表可以在任何会话中可见，但是会随着创建其的会话终⽌⽽DROP，其它会话这时就不能再引⽤全局临时表。
  6) 排序规则：表变量使⽤当前数据库的排序规则，临时表使⽤TempDb的排序规则。如果它们不兼容，你还需要在查询或者表定义中进⾏指定。
  7) 你如果希望在动态SQL中使⽤表变量，你必须在动态SQL中定义表变量。⽽临时表可以提前定义，在动态SQL中进⾏引⽤。
四、情景选择
  微软推荐使⽤表变量，如果表中的⾏数⾮常⼩，则使⽤表变量。很多”⽹络专家”会告诉你100是⼀个分界线，因为这是统计信息创建查询
计划效率⾼低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进⾏测试。很多⼈在⾃定义函数中使⽤表变量，如果你需要在表变量中使⽤主键和唯⼀索引，你会发现包含数千⾏的表变量也依然性能卓越。但如果你需要将表变量和其它表进⾏join，你会发现由于不精准的执⾏计划，性能往往会⾮常差。
  如果你需要在表建⽴后对表进⾏DLL操作，那么选择临时表吧。
  临时表和表变量有很多类似的地⽅。所以有时候并没有具体的细则规定如何选择哪⼀个。对任何特定的情况，你都需要考虑其各⾃优缺点并做⼀些性能测试。
五、表变量和临时表的对⽐
  下⾯的表格会让你⽐较其优略有了更详细的参考。
特性表变量临时表
作⽤域当前批处理当前会话，嵌套存储过程，全局：所有会话
使⽤场景⾃定义函数，存储过程，批处理⾃定义函数，存储过程，批处理
创建⽅式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 语句 (SQL 2000: 不能使⽤
INSERT/EXEC).INSERT 语句, 包括 INSERT/EXEC. SELECT INTO 语句.
Insert explicit values into identity
columns (SET IDENTITY_INSERT).
不⽀持SET IDENTITY_INSERT语句⽀持SET IDENTITY_INSERT语句
Truncate table不允许允许
truncate的特征
析构⽅式批处理结束后⾃动析构显式调⽤ DROP TABLE 语句.
当前会话结束⾃动析构 (全局临时表: 还包括当其它会话语
句不在引⽤表.)
事务只会在更新表的时候有事务，持续时间⽐临时
表短
正常的事务长度，⽐表变量长
存储过程重编译否会导致重编译
回滚不会被回滚影响会被回滚影响
统计数据不创建统计数据，所以所有的估计⾏数都为1,
所以⽣成执⾏计划会不精准
创建统计数据，通过实际的⾏数⽣成执⾏计划。
作为参数传⼊存储过程仅仅在SQL Server2008, 并且必须预定义
user-defined table type.
不允许
显式命名对象 (索引, 约束).不允许允许，但是要注意多⽤户的问题
动态SQL必须在动态SQL中定义表变量可以在调⽤动态SQL之前定义临时表

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。