【译】T-SQL进阶：超越基础（五）：在临时表中存储数据

原文链接：传送门。

有些时候，为复杂的业务需求返回数据需要你将一个或者多个结果集临时性的存储一段时间。通常这些临时表会存储当前的数据库链接域中，但是它们也许需要被多个数据库链接所共享。在本章我将讨论临时表，并展示给你几个如何使用这些临时表的示例。

什么是临时表

严格的说，什么是临时表？其实它也是一个表，不过其是在一个单独的业务处理域内进行创建，填充，使用，以及删除的。临时表，正如其名称暗示的那样，意味着它们的生命周期是短暂的并且当不再需要的时候便会被删除。临时表在Tempdb数据库中进行创建和存储，而且具有两种不同的作用域：本地和全局。

本地临时表是在一个单独的会话中创建和使用的。本地临时表仅仅对于创建它的会话是可用的。其他会话不能访问一个本地临时表。本地临时表通过其名称的第一个字符为一个单独的“#”进行识别。

另一方便，全局临时表对于所有的会话都是可用的，只要创建这个全局临时表的会话仍旧是活跃的。当一个表名称以“##“开头的时候，其便是一个全局临时表。

临时表的声明周期

正如之前已经提到的那样，临时表是短暂的，意味着它们来去匆匆。它们只是不会停留太久。

临时表（包括本地临时表和全局临时表）像其他任何表一样，是用CREATE TABLE语句进行创建的。但是既然它们是短暂的，那么它们便会在某一个时间点被自动删除。当创建它们的会话结束的时候，这些临时表便会自动被删除。但是你也可以手动的使用DROP TABLE语句来删除本地或者全局临时表，只要它们不再被需要。

局限

当你使用临时表的时候，有许多的局限是你应该要意识到的。这儿有一个局限的部分列表：

临时表不能进行分部
临时表不能引用以及被引用为一个FOREIGN KEY约束
当一个会话创建了一个临时表，其在一个事务中包含了一个命名约束，那么另一个会话便不能创建一个同名的临时表，直到第一个会话提交了那个事务
为了读取全局临时表和本地临时表的更多局限，请参考在线文档，其可以在这里被找到：http://technet.microsoft.com/en-us/library/ms174979.aspx。

使用临时表的示例

在这个部分，我将分享一些不同的例子来给你一个更好的如何使用临时表的体验。对于一些例子我将使用来源于AdventureWorks2008R2 数据库的一些数据。如果你想跟着并运行本文的一些例子，你可以从这儿下载AdventureWorks2008R2 数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587。

第一个例子，在列表1中，我将创建，填充一个临时表，并从其中查找出一些数据。

SET NOCOUNT ON;
CREATE TABLE #Person (
    BusinessEntityId int,
    Title         nvarchar(8),
    FirstName     nvarchar(50),
    LastName     nvarchar(50));
INSERT INTO #Person (BusinessEntityID, Title, FirstName, LastName) 
    SELECT TOP 10 BusinessEntityID, Title, FirstName, LastName
    FROM AdventureWOrks2008R2.Person.Person;
SELECT * FROM #Person;

列表1：创建，填充，删除一个本地临时表

在列表1的示例中，我首先创建了一个名为#Person的本地临时表。你可以说这是一个本地临时表，因为这个表的名称是以一个单独的#开头的。接下来我使用INSERT语句，其从AdventureWorks2008R2 库中的Person.Person表中选择了几个列来填充新创建的本地临时表。接下来我使用SELECT语句来显示被插入到 #Person临时表中的数据行。最后，我删除了这个本地临时表。如同你所看到的，创建一个本地临时表与创建任何其他表没有太大的不同。

对于第二个例子，让我们看看列表2的代码：

SET NOCOUNT ON;
CREATE TABLE ##SalesOrderHeader (
    SalesOrderId int,
    SalesOrderDate DateTime,
    CustomerId int);
INSERT INTO ##SalesOrderHeader (SalesOrderId, SalesOrderDate, CustomerId)
    SELECT SOH.SalesOrderID, SOH.OrderDate, C.CustomerID
    FROM AdventureWorks2008R2.Sales.SalesOrderHeader SOH
    LEFT OUTER JOIN AdventureWorks2008R2.Sales.Customer C
    ON  SOH.CustomerID = C.CustomerID;
SELECT TOP 10 * FROM ##SalesOrderHeader;
DROP TABLE ##SalesOrderHeader;

列表2：创建，使用，删除一个全局临时表

如果你查看列表2的代码，你可以看到我创建，使用，删除一个名为##SalesOrderHeader 的全局临时表就如同我在列表1中对于本地临时表所做的一样。唯一的不同便是列表2创建的代码是以两个”#”开头的，而不是一个。##SalesOrderHeader 表名中的两个 “#”告诉SQL SERVER这是一个全局临时表。

列表1和列表2的代码并没有展示出两种临时表在作用域上的差异。正如我们所陈述的，列表1的本地临时表的作用域是这个会话，而列表2的全局临时表的作用域是所有的会话。让我们来查看一个例子，其将帮助展示两种临时表的作用域的差异。

我将要运行的测试会创建三个不同的会话。每一个会话会运行一组不同SQL命令。对于这三个会话，我将使用SSMS的查询窗口。

为了开始测试，我在SSMS中打开了三个不同的窗口。接下来我记下每个窗口的会话ID。我能做它的一个方式便是在每个查询窗口中运行列表3的代码。

SELECT @@SPID;

列表3：识别每个会话的SESSION ID

我测试的下一步便是在一个查询窗口中创建一个本地临时表，而在另一个查询窗口中创建一个全局临时表。在最小会话ID的那个查询窗口中，我运行列表4的代码。

CREATE TABLE #Local (TableType varchar(10));
INSERT INTO #Local values ('Local');

列表4：创建并填充一个本地临时表

列表4的代码创建了一个本地临时表。

接下来我在具有较小SESSION ID的那个查询窗口中运行列表5中的代码。

CREATE TABLE ##Global (TableType varchar(10));
INSERT INTO ##Global values ('Global');

列表5：创建并填充一个全局临时表

列表5中的代码创建了一个全局临时表。

对于我测试的最后一步，我在具有最大SESSION ID的查询窗口中执行列表6的代码。

SET NOCOUNT ON;
SELECT * FROM ##Global;
GO
SELECT * FROM #Local;

列表6：从全局和本地临时表中选择数据

列表6中的代码引用了我在前两个会话中创建的本地临时表和全局临时表。当我运行列表6的代码并以文本模式展示我的输出，我得到了报告1的输出：

Global
Msg 208, Level 16, State 0, Line 3
Invalid object name '#Local'.

报告1：当列表6的代码运行后的输出

通过查看报告1的输出，你可以看到在这个会话中我可以引用到全局临时表，但不能引用到本地临时表。这展示了在一个会话中创建的本地临时表是如何在另一个会话中超出作用域的。以及全局临时表在没有创建它们的会话中仍然是可用的。另外，如果你关闭前两个会话窗口，然后再尝试运行列表6的代码，那么你将发现全局临时表也不再可用。

另一个本地临时表超出作用域的场景便是当你有嵌套的存储过程的时候。如果你阅读了SQL在线文档你会读到这样的一句话：临时表只在当前的会话中可见。实际上，只有当嵌套级别等于或大于创建临时表的嵌套级别时，临时表才在当前会话中可见。让我们通过运行列表7的代码来演示这个。

SET NOCOUNT ON;
GO
CREATE PROC MyProc 
AS
CREATE TABLE #Level1 (Level int);
INSERT #Level1 VALUES (1);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
SELECT * FROM #Level0;
SELECT * FROM #Level1;
GO
CREATE TABLE #Level0 (Level int);
INSERT #Level0 VALUES (0);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
EXEC MyProc;
SELECT * FROM #Level0;
SELECT * FROM #Level1;

列表7：在同一个会话，但不同的嵌套层次的临时表

通过查看与清单7关联的代码和输出，我们可以看到，当在较高的嵌套级别创建本地临时表时，较低的嵌套级别将无法使用该表，即使它们是在同一会话中创建的。在清单7中，我在嵌套级别0中创建并填充了一个名为#Level0的临时表，然后调用了在嵌套级别1中运行的存储过程MyProc。在该存储过程中，我创建并填充了一个名为#Level1的临时表。在同一个存储过程中，我显示了嵌套级别和SPID（会话ID），并从会话创建的两个本地临时表（#Level0和#Level1）中选择数据。我在MyProc中成功地查询了两个本地临时表，因为#Level0表是在较低的嵌套级别创建的，而#Level1表是在同一嵌套级别创建的。但当我的存储过程MyProc结束时，SQL Server会自动删除临时表Level1。这导致最后一个SELECT语句失败，并出现“Invalid object name”错误，因为我试图引用临时表Level1，该表是在比当前嵌套级别更高的嵌套级别中创建的。

最佳实践

当创建一个临时表时，这儿有一些最佳实践：

在你创建一个临时表之前，验证你不能创建一个基于集合的查询，其不需要创建一个临时表。（Before you create a temporary table, verify you can't create a set based query that does not require the creation of a temporary table.）
当它们不再需要的时候，请显式删除临时表。

总结

本地临时表是短期存储一个结果集的一个有效的方式，所以你可以在一个相同的会话中的额外处理中使用它。但请记住临时表会占据tempdb的空间，因此当你不再需要它们的时候你应该尽快删除它们。如果你需要你的临时表对于多个会话都是可用的，那你需要创建一个全局临时表。