[转]表变量和临时表的可比

正文转自;http://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html

   
关于表变量是什么(和表变量不是啊),以及跟临时表的比受洋洋人深纳闷。虽然网上已经来矣众多有关她的篇章,但我并从未意识一律篇比较完美的。在本篇文章被,我们用探讨表变量和临时表是呀(以及无是什么),然后我们由此采取临时表和表变量对那解密。

 

表变量

    表变量在SQL Server
2000挨首蹩脚吃引入,那么,什么是表变量呢?微软当BOL (Declare
@local_variable)中定义其为一个档次为表的变量。它的现实性定义包括列定义,列名,数据类型和约束。而于表变量中得行使的自律包括主键约束,唯一约束,Null约束与Check约束(外键约束不克在表变量中使用).定义表变量的话语是同健康使用Create
table定义表语句的子集。只是表变量通过DECLARE @local_variable
语句进行定义。

 

通过参考1足理解:

1)
表变量拥有一定作用域(在现阶段批处理报告句被,但不在旁当前批处理语句调用的储存过程和函数中),表变量在批处理完后活动为铲除。

 

2) 参考6未遭于”Recompilations
Due to Certain Temporary Table Operations”
环节讨论了临时表在会促成存储过程强制被再编译的各种原因,但这些原因并无适用于表变量。表变量和临时表比起来会起重复不见的囤积过程再编译。

 

3) 针对表变量的工作仅仅以创新数据时生效,所以锁和日志产生的数会再少。

 

4)
由于表变量的作用域如此之微,而且不属数据库的持久有,所以工作回滚不会见影响表变量。

 

   
表变量可以于那犯用域内像正规的申一样以。更确切的说,表变量可以给当成正规的表或者表表达式一样在select,delete,update,insert语句被应用。但是表变量不可知当接近“SELECT
select_list INTO table_variable” 这样的语中运用。而于SQL Server
2000遭,表变量也非能够给用于“INSERT INTO table_variable EXEC
stored_procedure”这样的说话中。

 

    表变量不可知召开如下事情:

    1.虽然表变量是一个变量,但是该非能够赋值给其他一个变量。

    2.check约,默认值,和计算列不能引用自定义函数。

    3.勿克吧约束命名。

    4.不能Truncate表变量

    5.休能够为标识列中插显式值(也就是说表变量不支持SET IDENTITY_INSERT
ON)

 

临时表

   
在深深临时表之前,我们先是得讨论一下会话(Session),一个会话仅仅是一个客户端到数码引擎的连年。在SQL
Server Management
Studio(SSMS)中,每一个询问窗口都见面及数据库引擎建立连接。一个应用程序可以同数据库建立一个要多独连续,除此之外,应用程序还可能建连接后一直无放直到应用程序结束,也或利用了释放连接要经常确立连接。

    那么,什么是临时表?在BOL (CREATE
TABLE)备受,我们得以理解临时表和为Create
table语句子创建的说明有一样的大体构成,但临时表与常规的表明不同之处有:

 

1)
临时表的名字不能够超过116个字符,这是由数据库引擎为了鉴别不同会话建立不同之临时表,所以会自动在临时表的名后附加一串

 

2)
局部临时表(以“#”开头命名的)作用域仅仅在目前底连年内,从以囤过程被成立有临时表的角度来拘禁,局部临时表会在下列情形于Drop:

    a.显式调用DROP Table语句

   
b.当局部临时表在囤过程外被创造时,存储过程结束吗就是象征部分临时表被DROP

    c.当前对话结束,在对话内创建的具有有临时表都见面叫Drop

 

3)
全局临时表(以“##”开头命名的)在装有的对话内可见,所以当开创全局临时表之前率先检查其是否有,否则一经就是,你以见面获更创建对象的错误.

   
a.全局临时表会在创立其的对话结束晚叫DROP,其它对话将不能够针对全局临时表进行引用。

    b.引用是当讲话级别进行,比如说下面例子:

        i.建立新的询问窗口,运行如下语句:

     create table ##temp (RowID int)

       
ii.再次打开一个新的查询创建,使用如下语句每5秒中针对全局临时表进行引用

     while 1=1 begin
       select * from ##temp
       waitfor delay '00:00:05'
     end

 

       iii.回到第一个窗口,关闭窗口

       iv.在产一个循环往复引用全局临时表时,将有错误

 

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中唯一的。6

   
参考6讨论了多由于临时表而造成的贮存过程又编译的原因及避免的章程。

 

误区

    误区1.表变量仅仅以内存中。

    误区2.临时表仅仅存储于物理介质中

   
这简单种意见都是显然的误区,在参考1的Q4省。表变量都是以TempDb数据库被开创,因为表变量存储的多少产生或超过物理内存。除此之外,我们发现而内存足够,表变量和临时表都见面在内存中开创和处理。它们啊同样可以当其余时间为存入磁盘。

    如何证明当时点?请圈下面代码(在SQL Server 2000届2008中犹灵验)

-- make a list of all of the user tables currently active in the

 -- TempDB database

 if object_id('tempdb..#tempTables') is not null drop table #tempTables

 select name into #tempTables from tempdb..sysobjects where type ='U'

 -- prove that even this new temporary table is in the list.

 -- Note the suffix at the end of it to uniquely identify the table across sessions.

 select * from #tempTables where name like '#tempTables%'

 GO

 -- create a table variable

 declare @MyTableVariable table (RowID int)

 -- show all of the new user tables in the TempDB database.

 select name from tempdb..sysobjects

  where type ='U' and name not in (select name from #tempTables)

 

    还有一部分“证明”临时表仅仅有吃内存中谬误,下面我来指出其中一个:

   
注意表变量的名字是系分配的,表变量的第一个字符”@”并无是一个字母,所以它并无是一个可行之变量名。系统会当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,但是其实的履行计划于临时表会根据每次存储过程的重编译而变更(看参考1,Q2有些).如果临时表不存,在变更执行计划的时候会时有发生错误。

 

2)
前面提到,一定立表变量后就是无法对该展开DDL语句操作。因此要用吗说明建立目录或者加同排列,你需要临时表。

 

3) 表变量不可知下select …into语句,而临时表可以

 

4) 在SQL Server
2008丁,你可将表变量作为参数传入存储过程。但是临时表不行。在SQL Server
2000暨2005备受表明变量也殊。

 

5)
作用域:表变量仅仅以眼前的批判处理着中,并且针对另在中嵌套的囤过程相当不可见。局部临时表只在脚下对话中中,这为囊括嵌套的存储过程。但针对大存储过程不可见。全局临时表可以以任何会话中可见,但是会趁着创建其的对话终止而DROP,其它对话这时就未克重新引用全局临时表。

 

6)
排序规则:表变量使用时数据库的排序规则,临时表使用TempDb的排序规则。如果它不般配,你还需以询问或者表定义中开展点名(参考7.Table
Variables and Temporary
Tables)

 

7)
你要愿意于动态SQL中应用表变量,你得在动态SQL中定义表变量。而临时表可以提前定义,在动态SQL中展开引用。

 

说了这么多,那么,我欠怎么选也?

   
微软推荐采取表变量(看参考4),如果表中之行数非常小,则用表变量。很多”网络大方”会告知您100凡一个分界线,因为这是统计信息创建查询计划效率高低之开。但是本人或盼望告知你针对你的一定需求对临时表和表变量进行测试。很多总人口于从定义函数中动用表变量,如果您用以表变量中以主键和唯一索引,你见面发觉含有数千行的表变量也依然性能突出。但只要您要将表变量和外表进行join,你晤面发现由于匪强准的施行计划,性能往往会十分不同。

   
为了验证这点,请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了博足够的测试数据,我拿此表中的数目插入了10尽。然后以ModifiedDate
列作为标准将临时表和表变量与原之Sales.SalesOrderDetail表进行了Join操作,从统计信息来拘禁IO差别明显。从日来看表变量做join花了50基本上秒,而临时表仅仅花费了8秒。

    如果你要在说明建立后对表展开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 不允许 允许
析构方式 批处理结束后自动析构 显式调用 DROP TABLE 语句.          当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)
事务 只会在更新表的时候有事务,持续时间比临时表短 正常的事务长度,比表变量长
存储过程重编译 会导致重编译
回滚 不会被回滚影响 会被回滚影响
统计数据 不创建统计数据,所以所有的估计行数都为1,所以生成执行计划会不精准 创建统计数据,通过实际的行数生成执行计划。
作为参数传入存储过程 仅仅在SQL Server2008, 并且必须预定义 user-defined table type. 不允许
显式命名对象 (索引, 约束). 不允许 允许,但是要注意多用户的问题
动态SQL 必须在动态SQL中定义表变量 可以在调用动态SQL之前定义临时表

 

参考:

1) INF: Frequently Asked Questions – SQL Server 2000 – Table
Variables

2) T-SQL BOL (SQL 2000), table data
type.aspx)

3) T-SQL BOL (SQL 2008), Declare
@local_variable

4) T-SQL BOL (SQL 2008), CREATE
TABLE

5) Table-Valued Parameters (Database
Engine)

6) Troubleshooting stored procedure
recompilation

7) Local Temporary Tables and Table
Variables

8) Startup stored
procedure

9) Data Definition Language
(DDL).aspx)

外值得看的篇章:

1) Things You Didn’t Know About Temp Tables and Table
Variables

 

 

 


初稿链接:http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

Translated by:CareySon

 

 

 

相关文章