sql: 临时表与表变量的区别

1、表变量

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

　　表变量的特点：

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

　　表变量可以在其作用域内像正常的表1样使用。更确切的说，表变量可以被当做正常的表或表表达式1样在SELECT,DELETE,UPDATE,INSERT语句中使用，但是表变量不能在类似"SELECT select_list INTO table_variable"这样的语句中使用。而在SQL Server2000中，表变量也不能用于INSERT INTO table_variable EXEC stored_procedure这样的语句中。

　　表变量不能做以下事情:

1. 虽然表变量是1个变量，但是其不能赋值给另外一个变量。
2. check束缚，默许值和计算列不能援用自定义函数。
3. 不能为束缚命名。
4. 不能Truncate表变量。
5. 不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)

2、临时表

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

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

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

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

　　3、全局临时表(以"##"开头命名的)在所有的会话内可见，所以在创建全局临时表之前首先检查其是不是存在，否则如果已存在，你将会得到重复创建对象的毛病。
　　　　a、全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进行援用。
　　　　b、援用是在语句级别进行，如:
　　　　　　1.新建查询窗口，运行语句：

　　　　　　2.再次新建1个查询窗口，每5秒援用1次全局临时表

　　　　　　3.回到第1个窗口，关闭窗口。
　　　　　　4.下1次第2个窗口援用时，将产生毛病。

　　4、不能对临时表进行分区。

　　5、不能对临时表加外键束缚。

　　6、临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象，而临时表属于TempDb)。由于TempDb在每次SQL Server重启后会被自动创建，所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也能够通过修改Model数据库来到达这1目标。

　　7、XML列不能定义成XML集合的情势，除非这个集合已在TempDb中定义。

　　临时表既可以通过Create Table语句创建，也能够通过"SELECT <select_list> INTO #table"语句创建。你还可以针对临时表用"INSERT INTO #table EXEC stored_procedure"这样的语句。
　　临时表可以具有命名的束缚和索引。但是，当两个用户在同1时间调用同1存储进程时，将会产生”There is already an object named ‘<objectname>’ in the database”这样的毛病。所以最好的做法是不用为建立的对象进行命名，而使用系统分配的在TempDb中唯1的。

3、误区

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

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

　　这两种观点都是毛病的，只有内存足够，表变量和临时表都会在内存中创建和处理。他们也一样可以在任什么时候间被存入磁盘。

　　注意表变量的名字是系统分配的，表变量的第1个字符”@”其实不是1个字母，所以它其实不是1个有效的变量名。系统会在TempDb中为表变量创建1个系统分配的名称，所以任何在sysobjects或sys.tables查找表变量的方法都会失败。

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

　　上述代码看上去貌似很好用，但会产生多用户的问题。你建立两个连接，在第1个连接中创建临时表，在第2个窗口中运行上面的语句能看到第1个连接创建的临时表，如果你在第2个连接中尝试操作这个临时表，那末可能会产生毛病，由于这个临时表不属于你的会话。

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

    这个误区也一样毛病。虽然1旦你创建1个表变量以后，就不能对其进行DDL语句了，这包括Create Index语句。但是你可以在表变量定义的时候为其创建索引)比如以下语句。

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

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

　　1) SQL 其实不能为表变量建立统计信息，就像其能为临时表建立统计信息1样。这意味着对表变量，履行引擎认为其只有1行，这也意味着针对表变量的履行计划其实不是最优。虽然估计的履行计划对表变量和临时表都为1，但是实际的履行计划对临时表会根据每次存储进程的重编译而改变。如果临时表不存在，在生成履行计划的时候会产生毛病。

　　2) 1旦建立表变量后就没法对其进行DDL语句操作。因此如果需要为表建立索引或加1列，你需要临时表。

　　3) 表变量不能使用select …into语句，而临时表可以。

　　4) 在SQL Server 2008中，你可以将表变量作为参数传入存储进程。但是临时表不行。在SQL Server 2000和2005中表变量也不行。

　　5) 作用域:表变量仅仅在当前的批处理中有效,并且对任何在其中嵌套的存储进程等不可见。局部临时表只在当前会话中有效，这也包括嵌套的存储进程。但对父存储进程不可见。全局临时表可以在任何会话中可见，但是会随着创建其的会话终止而DROP，其它会话这时候就不能再援用全局临时表。

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

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

4、如何选择

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

　　为了证明这点，请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了得到足够的测试数据，我将这个表中的数据插入了10遍。然后以ModifiedDate 列作为条件将临时表和表变量与原始的Sales.SalesOrderDetail表进行了Join操作，从统计信息来看IO差别显著。从时间来看表变量做join花了50多秒，而临时表仅仅花了8秒。

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

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

5、总结

出处:http://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html

公共表达式、临时表、表变量之间的区分

表变量：
表变量必须显示定义，不能使用SELECT * INTO 表变量或INSERT INTO 表变量 SELECT的方式创建
表变量的作用域（或说生命周期）在当前批处理语句中，1旦履行终了，被自动清除。
表变量存储在内存中，不支持非聚集索引，使用束缚、默许值、计算列的时候不能援用自定义函数，没有锁的机制，事务也只针对表变量更新数据，因此生成的日志很小。
当用户访问表变量的时候，不会产生日志。
表变量本身虽然是1个变量，但不能赋值给另外一个变量。
不能使用TRUNCATE命令。
表变量的统计信息是不可信的。
从MSSQL2008R2开始，表变量可以作为存储进程的参数。
表变量可以存在于内存和磁盘。
表变量使用当前数据库的排序规则。
动态SQL需要使用表变量的话，必须在动态SQL中定义。
 
临时表：
分为局部临时表（使用#开头）和全局临时表（使用##开头）。
局部临时表生命周期为当前会话，1旦会话结束会自动删除。
全局临时表生命周期为所有连接会话，但是当创建它的会话结束后，也会被自动删除。
临时表的名称不能超过116个字符，由于系统会自动在后面添加标识码。
临时表具有表的1般属性，但是不能分区，不能设置外键，临时表的列数据类型，必须是TempDB中存在的数据类型，换句话说，临时表不能使用自定义数据类型。（自定义数据类型是数据库级别，而临时表只存在于TempDB数据库）
临时表可使用CREATE、SELECT * INTO、INSERT INTO SELECT的方式创建。
临时表上的统计是相对可信的。
临时表可以存在于内存和磁盘。
临时表使用TempDB数据库的排序规则。
可以先创建临时表，然后在动态SQL中使用。
 
公共表达式（CTE）：
CTE只是1个履行结果集，结果集，结果集（重要的话说3遍）。
CTE存在于外部查询中，它对同1批处理中的其他语句不可见。
CTE不能被嵌套，也就是CTE里面不能再定义新的CTE。
同1批语句中可以创建多个CTE，后面的CTE可以援用前面的CTE。
可以在1次查询中屡次援用同1个CTE。
对CTE中记录的删除、修改一样可以作用到CTE对应的数据表。
CTE可以递归使用。

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠