在一些先決條件下，SQL Server可以緩存臨時表（cache Temp Tables）。緩存臨時表意味著當你創建反復創建同個臨時表時，SQL Server就可以重用它們。這會從整體上大幅度提高你的工作量（性能），因為SQL Server不需要訪問內存里的特定頁（PFS，GAM，SGAM），經常訪問這些頁在工作量大的情況下會引起加鎖競爭（Latch Contention）。Paul White有一篇很棒的文章詳細描述這個情況，可以點此圍觀下。

臨時表緩存的條件之一是不能在存儲過程里混合使用DML（Data Manipulation Language 數據操作語言）和DDL（Data Definition Language 數據定義語言）語句。我們來看下面的代碼：

 1 -- Create a new stored PRocedure 2 CREATE PROCEDURE PopulateTempTable 3 AS 4 BEGIN 5     -- Create a new temp table 6     CREATE TABLE #TempTable 7     ( 8         Col1 INT IDENTITY(1, 1), 9         Col2 CHAR(4000),10         Col3 CHAR(4000)11     )12 13     -- Create a unique clustered index on the previous created temp table14     CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)15 16     -- Insert 10 dummy records17     DECLARE @i INT = 018     WHILE (@i < 10)19     BEGIN20         INSERT INTO #TempTable VALUES ('Woody', 'Tu')21         SET @i += 122     END23 END24 GO

這里你通過DDL語句（CREATE UNIQUE CLUSTERED INDEX）創建了索引，這就是說你混合使用了DDL和DML語句。因此SQL Server不能緩存你的臨時表。你可以從下面例子里的DMV sys.dm_os_performance_counters，通過跟蹤性能計數器Temp Tables Creation Rate來驗證：

 1 DECLARE @table_counter_before_test BIGINT; 2 SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters 3 WHERE counter_name = 'Temp Tables Creation Rate' 4  5 DECLARE @i INT = 0 6 WHILE (@i < 1000) 7 BEGIN 8     EXEC PopulateTempTable 9     SET @i += 110 END11 12 DECLARE @table_counter_after_test BIGINT;13 SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters14 WHERE counter_name = 'Temp Tables Creation Rate'15 16 PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test)17 GO

當你運行這個代碼時，SQL Server需要創建1000個單獨的臨時表，這個從SSMS的輸出窗口就可以看到。

通過PRIMARY KEY約束來強制UNIQUE CLUSTERED INDEX就很容易克服這個問題。在這個方式下，你沒有混合使用DDL和DML語句，SQL Server最后也能緩存你的臨時表。

 1 ALTER PROCEDURE PopulateTempTable 2 AS 3 BEGIN 4     -- Create a new temp table 5     CREATE TABLE #TempTable 6     ( 7         Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index 8         Col2 CHAR(4000), 9         Col3 CHAR(4000)10     )11 12     -- Insert 10 dummy records13     DECLARE @i INT = 014     WHILE (@i < 10)15     BEGIN16         INSERT INTO #TempTable VALUES ('Woody', 'Tu')17         SET @i += 118     END19 END20 GO

當你重新執行剛才用來跟蹤相關計數器的代碼，可以看到SQL Server值創建了一次臨時表并重用它了：

這個結論也意味著，當你創建額外的非聚集索引（Non-Clustered Indexes）時，SQL Server也不能緩存臨時表，因為在你的存儲過程里，你又一次混合使用DDL和DML語句。

但在SQL Server 2014里，你就可以克服這個限制，因為現在你可以在CREATE TABLE語句行里創建索引。來看下面的代碼：

 1 ALTER PROCEDURE PopulateTempTable 2 AS 3 BEGIN 4     -- Create a new temp table 5     CREATE TABLE #TempTable 6     ( 7         Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index 8         Col2 CHAR(100) INDEX idx_Col2, 9         Col3 CHAR(100) INDEX idx_Col310     )11 12     -- Insert 10 dummy records13     DECLARE @i INT = 014     WHILE (@i < 10)15     BEGIN16         INSERT INTO #TempTable VALUES ('Woody', 'Tu')17         SET @i += 118     END19 END20 GO

如你所見，我在創建臨時表本身的時候，就在臨時表上直接創建2個額外的非聚集索引。又一次我們沒有混合使用DDL和DML語句，SQL Server又一次可以緩存并重用你的臨時表。

在SQL Server 2014里，在臨時表上定義行內定義索引，避開混合使用DML和DDL語句，讓臨時表只創建一次并重用，是一個很棒的功能！

這個新功能怎樣？歡迎在下面評論里告訴我。