insert into 两张表_数据库大师成长日记：如何在SQL中正确使用表变量

经常写数据库SQL脚本的朋友，对数据库的本地表(table)肯定不会陌生，我们的数据，大多都是通过表进行存储。除了本地表之外，功能比较类似的还包括临时表、表变量、公用表、表类型、内存表等，她们的含义和用途各不相同。今天我们就好好说说SQLServer中的表变量。

什么是表变量？

顾名思义，表变量是一种特殊的表，同时又是一种特殊的变量。表变量从SQL Server 2000开始引入，主要应用在存储过程和自定义函数中。

表变量首先是变量，这就决定了其作用域主要在存储过程和自定义函数内部。您在使用的时候，需要先定义后使用，并不需要而且无法通过脚本释放，表变量在存储过程和自定函数执行结束时自动释放。这一点与内部临时表类似。

表变量是一种特殊的表，与定与本地表结构的方法类似，需要定义表名、字段、字段类型等。但表变量与本地表和临时表存在本质的区别。可以理解为表变量主要是在内存中暂存(有时候也会像临时表一样存入tempdb中)用完即焚，而本地表和临时表真实存在于数据库中。

为什么要使用表变量？

表变量与本地表和临时表相比，具有如下优点：

• 与其它变量的定义一样，表变量具有良好的作用范围，并会被自动清除。
• 在存储过程中使用表变量会减少自定义函数和存储过程的重新编译。
• 表变量需要更少的锁请求和日志资源。表变量脱离了事务机制的范围，事务日志不会记录。
• 可以在表变量上使用UDF、UDDT、XML，使用和操作与本地表和临时表非常类似。

表变量如何创建？

表变量首先是变量，使用declare定义，不可使用create创建。语法如下：

declare @表名称 table( 字段1 类型, 字段2 类型, ...);

比如我们创建一个销售表表变量：

declare @sale table( FName nvarchar(50), FDistrict nvarchar(50), FAmount decimal(28,10));

如何向表变量中插入数据？

向表变量中插入数据，与向本地表和临时表中插入数据一样，使用insert语句，可以直接插入数据，也可以使用insert select插入查询结果。比如：

insert into @sale(FName,FDistrict,FAmount) values('张三','北京',20000),('张三','上海',50000),('张三','深圳',40000),('张三','广州',30000),('李四','北京',30000),('李四','上海',50000),('李四','深圳',40000),('李四','广州',10000)；

如何查询表变量的数据？

查询表变量的数据，与查询本地表和临时表的方法一致，使用select语句。比如：

select * from @sale where FName='张三';

如何更新和删除表变量中的数据？

更新或删除表变量中的数据，与更新或删除本地表和临时表的数据方法一样，使用update和delete语句。比如：

update @sale set FAmount=10000 where FName='张三' and FDistrict='深圳';delete @sale where FName='张三';

表变量的限制(缺点)有哪些？

与本地表和临时表相比，表变量存在着如下缺点：

• 在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划。
• 不能直接在表变量上创建索引，但可以通过创建约束(主键、唯一)来建立索引。
• 在declare后，不能再对表变量结构进行更改。
• 表变量不支持select ... into表变量语句。
• SQLServer2000版本不能对表变量执行insert into ... exec 存储过程。
• 不能通过exec或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。

什么场景下最适合使用表变量？

表变量主要应用在存储过程或自定义函数中，使用简单、数据量较少时执行速度快，而且无需释放，但如果处理的数据太多，会严重占用内存，性能反而会变低。

经过反复测试表明，如果表变量存储和处理的数据不超过1000行，其性能明显优于本地表和临时表，如果数据达到数万行，使用临时表可能会获得更高的执行效率。