在SQL Server的性能调优中，有一个不可比面的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集？表变量和临时表是两种选择。
表变量
表变量是一种特殊的数据类型，是变量的一种，可用于存储结果集以进行后续处理。表主要用于临时存储结果集返回的行。如果声明函数和变量的类型为表，则表变量可在函数、存储的过程和批处理。
创建表变量语法
DECLARE @<表变量名>
Table( { <column_definition> | <table_constraint> } [ ,...n ] )
<column_definition> ::=
column_name scalar_data_type
[ COLLATE <collation_definition> ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
[ ROWGUIDCOL ]
[ column_constraint ] [ ...n ]
<column_constraint> ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}
<table_constraint> ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
| CHECK ( logical_expression )
}
表变量一般备注
- 表可以像一般的 FROM 子句中按名称引用变量，如下面的示例所示︰
SELECT Employee_ID, Department_ID FROM @MyTableVar;
- 若有关联查询，则在FROM 子句，外部表必须使用一个别名，来引用变量，如下面的示例中所示︰
SELECT EmployeeID, DepartmentID
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
m.DepartmentID = Employee.DepartmentID);
- 表变量行为类似于本地变量。 有明确定义的作用域。 这就是在其中声明该变量的函数、存储过程或批处理。在其范围内，表可像常规表中使用变量。 该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的任何地方。在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量；
- 表变量，使用系统内存，读写速度快，但内存是有一定限制，所有操作无日志。
- 表变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载；
- 表变量并不是都存在于内存中，表变量存放在内存是有一定限制的，如果表变量数据量超过阈值，会把内存耗尽，然后使用TempDB的空间。
- 由于表变量不会写日志，不会造成锁开销，不能在Declare之外创建主键索引等，因此表变量不会造成架构的变化，从而不会造成重编译。该存储过程的执行计划已经在创建存储过程的时候生成了，因此之后执行的存储过程使用表变量不会造成执行计划的重编译。
表变量的限制：
- 不能对表变量执行SELECT INTO语句，如：
SELECT select_list INTO table_variable;
- 在SQL Server2000中，表变量也不能用于INSERT INTO table_variable EXEC stored_procedure这样的语句中，而在2005之后的版本就开始支持这个用法了。
- 在DECLARE后，不能再对表变量进行更改，即无法进行DDL操作；
- 不能直接在表变量上创建索引(因为一旦你创建一个表变量之后，就不能对其进行DDL语句了，这包括Create Index语句)，但可以通过创建约束（主键、唯一）来建立索引；
- 表变量声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。
- 表变量不支持变量之间的赋值操作；
- 因为表变量具有有限的范围，并不是持久的数据库的一部分，它们不受事务回滚；
- 表变量存在于内存，当大数据量时，使用表变量的话就太耗内存了；
- 在表变量上不能创建非聚集索引（为 PRIMARY 或 UNIQUE 约束创建的系统索引除外）。与具有非聚集索引的临时表相比，这可能会影响查询性能；
- 表变量不具有数据分布的统计信息，它们不会触发重新编译。在许多情况下，优化器会在假定 table 变量没有行的前提下生成查询计划。这样不利于优化器做出正确的执行计划，不适合数据量较大的情况。
- 如果表变量是在 EXEC 语句或 sp_executesql 存储过程外创建的，则不能使用 EXEC 语句或sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用
【示例】
(1)表变量的创建与查询
-- Create the table variable.
DECLARE @MyTableVar table(
LocationID int NOT NULL,
CostRate smallmoney NOT NULL,
NewCostRate AS CostRate * 1.5,
ModifiedDate datetime);
-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
SELECT LocationID, CostRate, GETDATE() FROM Production.Location
WHERE CostRate > 0;
-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO
(2)在INSERT..EXEC中使用表变量
--获取借书记录分析情况
create proc sp_GetBorrowAnalysis
@BeginTime datetime,
@EndTime datetime
as
--建一个表变量，结构与sp_GetBorrowRecord查询出的结果集相同
declare @Record table
(
BookID int, --书籍ID
BookName varchar(100), --书籍名称
TypeID int, --书籍类别ID
CardID int --借书卡ID
CardName varchar(100) --借书人姓名
)
--获取这段时间内的借书记录，并存入@Record表变量中
insert into @Record exec sp_GetBorrowRecord @BeginTime,@EndTime
临时表
临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。
创建临时表格式的两种方式
①CREATE TABLE
CREATE TABLE #|##
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> } [ ,...n ] )
- 临时对象都以#或##为前缀，以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问；
- 创建临时表的方法和创建普通表一样，除了有以下不同：
- 多了#|##前缀；
- 当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其他所有约束定义；
- 如果临时表中指定了 FOREIGN KEY 约束，则该语句将返回一条表明已跳过此约束的警告消息。 此表仍将创建，但不使用 FOREIGN KEY 约束。 在 FOREIGN KEY 约束中不能引用临时表；
②SELECT INTO
使用SELECT INTO会自动生成临时表，不需要事先创建
SELECT <select_list>
INTO #|##<临时表名>
FROM <table_source>
WHERE <search_condition>
临时表一般备注
- 临时表的用法(如INSERT、UPDATE、DELETE)和一般的表一样；
- 临时表不能分区；
- 如果在单个存储过程或批处理中创建了多个临时表，则它们必须有不同的名称；
- 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定；
- 如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则数据库引擎必须能够区分由不同用户创建的表。 为此，数据库引擎在内部为每个本地临时表的表名追加一个数字后缀；
- 临时表以会话为边界，除非使用 DROP TABLE 显式删除临时表，否则临时表将在退出其作用域时由系统自动删除；
局部临时表会在下列情况下被Drop：
a、显式调用Drop Table语句；
b、当存储过程完成时，将自动删除在存储过程中创建的本地临时表。
c、当前会话结束，在会话内创建的所有局部临时表都会被Drop；
全局临时表会在下列情况下被Drop：
a、全局临时表在创建此表的会话结束且其他所有任务停止对其引用时将被自动删除。 换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动删除此表。
- 临时表存储在TempDb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的；
临时表的约束
- 不能对临时表进行分区；
- 不能对临时表加外键约束；
- 临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象，而临时表属于TempDb)
同名临时表
从一般备注我们知道，如果本地临时表由存储过程创建，数据库引擎在内部为每个本地临时表的表名追加一个数字后缀。当存储过程完成时,将自动除去在存储过程中创建的本地临时表。所以即使在存储过程或触发器中创建的本地临时表的名称可以与在调用存储过程或触发器之前创建的临时表名称相同，但是临时表在会话中只是一个代号，在实际的系统临时库中，真实表名会自动处理，所以它们也是不同的。
在存储过程或触发器中创建的本地临时表的名称可以与在调用存储过程或触发器之前创建的临时表名称相同。 但是，如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该查询。
嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表，套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所创建的表。但是，为了对其进行修改以解析为在嵌套过程中创建的表，此表必须与调用过程创建的表具有相同的结构和列名。
CREATE PROCEDURE dbo.Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT Test2Col = x FROM #t;
GO
CREATE PROCEDURE dbo.Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
EXEC Test1;
GO
下面是结果集：
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
【示例】
drop table #Tmp --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(
ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
WokNo varchar(50),
primary key (ID) --定义ID为临时表#Tmp的主键
);
Select * from #Tmp --查询临时表的数据
truncate table #Tmp --清空临时表的所有数据和约束
表变量与临时表的对比
- 临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存。在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。所以数据量比较少的时候可以使用表变量，数据量大时一般推荐使用临时表。
- 表变量缺省放在内存，速度快，因此建议触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。
- 无表关联操作，只作为中间集进行数据处理，建议用表变量；有表关联，且不能确定数据量大小的情况下，建议用临时表。
- 表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了，方便。
- 全局临时表的功能是表变量没法达到的。全局临时表可在多个会话中使用。
- 表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。
- 在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
- 事务支持：临时表:支持，表变量:不支持
