SQL Server 表变量和临时表的区别(详细补充篇)

一、表变量

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

nbsp;　 下面来玩玩表变量吧。

　　定义一个表变量，插入一条数据，然后查询：

DECLARE @tb1 Table
　　(
 　　Id int,
 　　Name varchar(20),
 　　Age int
　　)

　　INSERT INTO @tb1 VALUES(1,'刘备',22)

　　SELECT * FROM @tb1

输出结果如下：

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

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

　ALTER TABLE @tb1
 ADD CONSTRAINT CN_AccountAge
 CHECK 
 (Account_Age gt; 18); -- 插入年龄必须大于18

SQL Server提示错误如下：

SQL Server不支持定义表变量时对Constraint命名，也不支持定义表变量后，对其建Constraint。

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

二、临时表

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

　　临时表和Create Table语句创建的表有着相同的物理工程，但临时表与正常的表不同之处有：

　　1、临时表的名称不能超过116个字符，这是由于数据库引擎为了辨别不同会话建立不同的临时表，所以会自动在临时表的名字后附加一串。

　　2、局部临时表(以"#"开头命名的)作用域仅仅在当前的连接内，从在存储过程中建立局部临时表的角度来看，局部临时表会在下列情况下被Drop：
　　　　a、显示调用Drop Table语句
　　　　b、当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被Drop。
　　　　c、当前会话结束，在会话内创建的所有局部临时表都会被Drop。

　　3、全局临时表(以"##"开头命名的)在所有的会话内可见，所以在创建全局临时表之前首先检查其是否存在，否则如果已经存在，你将会得到重复创建对象的错误。
　　　　a、全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进行引用。
　　　　b、引用是在语句级别进行，如:
　　　　　　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 lt;select_listgt; INTO #table"语句创建。你还可以针对临时表用"INSERT INTO #table EXEC stored_procedure"这样的语句。
nbsp;临时表可以拥有命名的约束和索引。但是，当两个用户在同一时间调用同一存储过程时，将会产生”There is already an object named ‘lt;objectnamegt;' in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名，而使用系统分配的在TempDb中唯一的。

三、误区

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

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

这两种观点都是错误的，只有内存足够，表变量和临时表都会在内存中创建和处理。他们也同样可以在任何时间被存入磁盘。
　　注意表变量的名字是系统分配的，表变量的第一个字符”@”并不是一个字母，所以它并不是一个有效的变量名。系统会在TempDb中为表变量创建一个系统分配的名称，所以任何在sysobjects或sys.tables查找表变量的方法都会失败。

　　正确的方法应该是我前面例子中的方法，我看到很多人使用如下查询查表变量：

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

（编辑：南京站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!