SQL Server[转]表变量和暂且表的相比较

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

   
关于表变量是什么(和表变量不是什么),以及和方今表的可比让不少人至极纳闷。即便网上早就有了无数关于它们的稿子,但自个儿并从未意识一篇相比周到的。在本篇文章中,大家将探索表变量和一时表是怎么(以及不是如何),然后大家透过动用如今表和表变量对其解密。

 

表变量

    表变量在SQL Server
3000中第贰次被引入,那么,什么是表变量呢?微软在BOL (Declare
@local_variable)
中定义其为1个品类为表的变量。它的有血有肉定义包含列定义,列名,数据类型和自律。而在表变量中能够运用的封锁蕴含主键约束,唯一约束,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
两千中,表变量也不能被用于“INSE途睿欧T INTO table_variable EXEC
stored_procedure”那样的言语中。

 

    表变量无法做如下事情:

    1.虽说表变量是1个变量,然而其无法赋值给另三个变量。

    2.check约束,暗许值,和总计列无法引用自定义函数。

    3.不能够为束缚命名。

    4.不能Truncate表变量

    5.不可能向标识列中插入显式值(也等于说表变量不帮助SET IDENTITY_INSERT
ON)

 

临时表

   
在长远一时半刻表此前,大家率先须要斟酌一下会话(Session),二个会话仅仅是三个客户端到数量引擎的一连。在SQL
Server Management
Studio(SSMS)中,每二个询问窗口都会和数据库引擎建立连接。2个应用程序能够和数据库建立三个或两个三番五次,除此之外,应用程序还也许建立连接后直接不自由直到应用程序甘休,也说不定采用完释放连接须要时创造连接。

    那么,什么是一时表?在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.再次打开3个新的查询创立,使用如下语句每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”语句创立。你仍是能够针对权且表使用”INSE奥迪Q5T 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 两千到二零零六中都灵验)

-- 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)

 

    还有局地“评释”一时半刻表仅仅存在于内存中谬误,下边小编来建议当中1个:

   
注意表变量的名字是系统一分配配的,表变量的首先个字符”@”并不是一个字母,所以它并不是二个实用的变量名。系统会在TempDb中为表变量创设3个系统一分配配的名称,所以任何在sysobjects或sys.tables查找表变量的法门都会失利。

   
正确的不二法门应该是自家后面例子中的方法,作者来看见惯不惊人接纳如下查询查表变量:

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

   
上述代码看上去貌似很好用,但会时有发生多用户的标题。你建立五个一连,在第①个接二连三中创建权且表,在其次个窗口中运作方面包车型客车口舌能看出第二个一而再创建的暂时表,要是您在其次个三番五次中尝试操作那一个权且表,那么或然会时有爆发错误,因为那么些一时半刻表不属于您的对话。

 

误区3.表变量不可能有所索引。

   
这么些误区也一律错误。尽管一旦您创建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
二〇〇八中,你能够将表变量作为参数字传送入存款和储蓄进程。可是方今表不行。在SQL Server
两千和二〇〇七中表变量也万分。

 

5)
效能域:表变量仅仅在眼下的批处理中有效,并且对任何在里面嵌套的蕴藏进程等不可见。局地一时表只在当下对话中有效,这也包蕴嵌套的储存进度。但对父存款和储蓄进度不可见。全局暂时表可以在别的会话中可知,不过会趁着成立其的对话终止而DROP,别的对话那时就不能够再引用全局暂且表。

 

6)
排序规则:表变量使用当前数据库的排序规则,权且表使用TempDb的排序规则。就算它们不合作,你还须要在查询可能表定义中开始展览点名(参考7.Table
Variables and Temporary
Tables
)

 

7)
你只要期望在动态SQL中选用表变量,你必须在动态SQL中定义表变量。而权且表可以提前定义,在动态SQL中开展引用。

 

说了那样多,那么,笔者该怎么抉择呢?

   
微软推荐使用表变量(看参考4),假使表中的行数十分的小,则应用表变量。很多”网络大方”会报告你100是一个分界线,因为那是总计消息创立查询安排功效高低的启幕。但是自身照旧希望告知你针对你的一定供给对权且表和表变量实行测试。很多少人在自定义函数中动用表变量,如若您须求在表变量中采用主键和唯一索引,你会意识带有数千行的表变量也照旧品质出众。但万一您供给将表变量和任何表实行join,你会发现由于不精准的施行安排,质量往往会至极差。

   
为了证实那一点,请看本文的附属类小部件。附属类小部件中代码创制了表变量和权且表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了获得丰盛的测试数据,小编将以此表中的数码插入了1伍遍。然后以ModifiedDate
列作为标元帅权且表和表变量与原本的Sales.SalesOrderDetail表举行了Join操作,从总计音信来看IO差距显然。从时间来看表变量做join花了50多秒,而一时半刻表仅仅花了8秒。

    假设你供给在表建立后对表展开DLL操作,那么选拔暂时表吧。

   
近来表和表变量有广大好像的地点。所以有时并不曾实际的细则规定何以抉择哪3个。对别的特定的图景,你都急需考虑其分别优缺点并做一些质量测试。下边包车型客车报表会让你相比较其优略有了更详实的参考。

 

总结

特性 表变量 临时表
作用域 当前批处理 当前会话,嵌套存储过程,全局:所有会话
使用场景 自定义函数,存储过程,批处理 自定义函数,存储过程,批处理
创建方式 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

SQL Server,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

 

 

 

相关文章