表变量

    表变量在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)

 

 

说了如此多,那么,我该咋样挑选吧?

   
微软推荐使用表变量(看参考4),假如表中之行数极度小,则利用表变量。很多”网络大方”会告知您100是一个分界线,因为就是总括信息创建查询计划效用高低之起。但是自或者盼望告知你针对你的特定需求对临时表和表变量举办测试。很多口于打定义函数中使表变量,倘使您待以表变量中采纳主键和唯一索引,你谋面发觉带有数千举行之表变量也还性能出众。但尽管你用拿表变量和另表展开join,你会晤发觉由于未强准的施行计划,性能往往会死例外。

   
为了求证这一点,请看本文的附件。附件中代码创造了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了拿走丰硕的测试数据,我将之表中的数量插入了10普。然后以ModifiedDate
列作为基少校临时表和表变量与原来的Sales.SalesOrderDetail表举行了Join操作,从总计信息来拘禁IO差异显然。从日来看表变量做join花了50基本上秒,而临时表仅仅花费了8秒。

    假若您需要以声明建立后针对发明举办DLL操作,那么采取临时表吧。

   
临时表和表变量有为数不少像样之地方。所以有时并无实际的细则规定怎么拔取哪一个。对另特定的情况,你都要考虑其分别优缺点并举办有特性测试。下边的表格会于您相比较其优略有了双重详实的参阅。

 

   
关于表变量是呀(和表变量不是呀),以及同临时表的比让洋洋丁非常纳闷。即便网上早已来矣累累有关其的著作,但自己连不曾发觉同篇相比较健全的。在本篇作品被,大家拿探索表变量和临时表是啊(以及不是啊),然后我们经过利用临时表和表变量对这解密。

误区

    误区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中举行引用。

 

总结

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

 

 

 

临时表

   
在深远临时表在此之前,我们先是需探究一下会话(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议论了不少出于临时表而致的蕴藏过程还编译的原故和避免的方法。

 

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

相关文章

网站地图xml地图