DECLARE @T AS TABLE(ID INT PRIMARY KEY,Name NVARCHAR(50))IF OBJECT_ID('tempdb..#T') IS NOT NULL  DROP TABLE #TCREATE TABLE #T (ID INT PRIMARY KEY,Name NVARCHAR(50))ALTER TABLE #T ADD Col1 nvarchar(50);SELECT * FROM #TALTER TABLE @T1 ADD Col1 nvarchar(50);SELECT * FROM @T

下面来玩玩表变量吧。

 

在深入临时表之前,我们要了解一下会话(Session),一个会话仅仅是一个客户端到数据引擎的连接。在SQL
Server Management
Studio中,每一个查询窗口都会和数据库引擎建立连接。一个应用程序可以和数据库建立一个或多个连接,除此之外,应用程序还可能建立连接后一直不释放知道应用程序结束,也可能使用完释放连接需要时建立连接。

 

3.回到第一个窗口,关闭窗口。4.下一次第二个窗口引用时,将产生错误。

4、临时表可以创建索引,表变量是不行的,所以中间缓存结果会比较大的时候,可以适当使用临时表创建索引来进行优化。

微软推荐使用表变量,如果表中的行数非常小,则使用表变量。很多”网络专家”会告诉你100是一个分界线,因为这是统计信息创建查询计划效率高低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进行测试。很多人在自定义函数中使用表变量,如果你需要在表变量中使用主键和唯一索引,你会发现包含数千行的表变量也依然性能卓越。但如果你需要将表变量和其它表进行join,你会发现由于不精准的执行计划,性能往往会非常差。

1、在事务里面的影响,临时表可以被回滚 而表变量是不能回滚的。简单的例子

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

 

如果你需要在表建立后对表进行DLL操作,那么选择临时表吧。

暂时想到这么多╮(╯_╰)╭,如果想到新的东西再分享

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

 

四、如何选择

5、表变量不维护统计数据。这意味着任何表变量数据更改都不会引起相关查询语句进行重编译

declare @temp TABLE ( RowID int NOT NULL, ColA int NOT NULL, ColB char(1)UNIQUE, PRIMARY KEY CLUSTERED(RowID, ColA))

3、临时表可以定义外键,但是表变量是不行的(这里代码就不写了╮(╯_╰)╭,)

7)
你如果希望在动态SQL中使用表变量,你必须在动态SQL中定义表变量。而临时表可以提前定义,在动态SQL中进行引用。

消息 102,级别 15,状态 1,第 16 行
“@T1”附近有语法错误。
消息 102,级别 15,状态 1,第 16 行
“Col1”附近有语法错误。

6)
排序规则:表变量使用当前数据库的排序规则,临时表使用TempDb的排序规则。如果它们不兼容,你还需要在查询或者表定义中进行指定。

DECLARE @T AS TABLE(ID INT,Name NVARCHAR(50))IF OBJECT_ID('tempdb..#T') IS NOT NULL  DROP TABLE #TCREATE TABLE #T (ID INT,Name NVARCHAR(50))BEGIN TRAN  INSERT INTO @T      ( ID, Name )  VALUES ( 1,N'Col1' );  INSERT INTO #T      ( ID, Name )  VALUES ( 1,N'Col1' );ROLLBACKSELECT * FROM @TSELECT * FROM #T@TID     Name----------- --------------------------------------------------1      Col1#TID     Name----------- --------------------------------------------------

更多的不允许,请查看上面的要求。

又是想到什么写什么系列……上次 
写了一下,今天补充点其它的

二、临时表

2、临时表定义好之后可以通过DDL语句修改结构,但是表变量就不行了

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

 

再来试试一些不符合要求的情况,例如添加表变量后,添加约束,并对约束命名:

6、表变量可以引用用户自定义数据类型,临时表并不能,因为临时表引用的是tempdb,而里面并没有定义该用户自定义类型。

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

INSERT 语句, 包括 INSERT/EXEC.

表变量的特征:1.表变量拥有特定作用域(在当前批处理语句中,但不在任何当前批处理语句调用的存储过程和函数中),表变量在批处理结束后自动被清除。2.表变量较临时表产生更少的存储过程重编译。3.针对表变量的事务仅仅在更新数据时生效,所以锁和日志产生的数量会更少。4.由于表变量的作用域如此之小,而且不属于数据库的持久部分,所以事务回滚不会影响表变量。

SELECT INTO 语句.

可以使用自定义数据类型

特性 表变量 临时表 作用域 当前批处理
当前会话,嵌套存储过程,全局:所有会话 使用场景
自定义函数,存储过程,批处理 自定义函数,存储过程,批处理 创建方式
DECLARE statement only.只能通过DECLEARE语句创建

再来试试一些不符合要求的情况,例如添加表变量后,添加约束,并对约束命名:

这个语句将会创建一个拥有聚集索引的表变量。由于主键有了对应的聚集索引,所以一个系统命名的索引将会被创建在RowID列上。

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

临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况,你都需要考虑其各自优缺点并做一些性能测试。下面的表格会让你比较其优略有了更详细的参考。

自定义数据类型和XML集合必须在TempDb内定义 Collation
字符串排序规则继承自当前数据库 字符串排序规则继承自TempDb数据库 索引
索引必须在表定义时建立 索引可以在表创建后建立 约束 PRIMARY KEY, UNIQUE,
NULL, CHECK约束可以使用,但必须在表建立时声明 PRIMARY KEY, UNIQUE, NULL,
CHECK. 约束可以使用,可以在任何时后添加,但不能有外键约束
表建立后使用DDL (索引,列) 不允许 允许. 数据插入方式 INSERT 语句 (SQL
2000: 不能使用INSERT/EXEC).

可以使用XML集合

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之前定义临时表

表变量在SQL Server
2000中首次被引入。表变量的具体定义包括列定义,列名,数据类型和约束。而在表变量中可以使用的约束包括主键约束,唯一约束,NULL约束和CHECK约束(外键约束不能在表变量中使用)。定义表变量的语句是和正常使用Create
Table定义表语句的子集。只是表变量通过DECLARE
@local_variable语句进行定义。

下面的例子演示你可以在一个表变量的列上创建唯一约束以及如何建立复合索引。

表名长度 最多128字节 最多116字节 列类型

相关文章