Under some preconditions SQL Server is able to cache Temp Tables. Caching Temp Tables means that SQL Server doesn’t have to recreate them, when you are creating the same Temp Table over and over again. This can improve the throughout of your workload tremendous, because SQL Server doesn’t has to access specialized pages in memory (PFS, GAM, SGAM), which would lead to Latch Contention under a high workload. Paul White has an excellent blog posting which describes that behavior in *more* detail 😉

One of the requirements of Temp Table Caching is that you can’t mix DML with DDL statements in a Stored Procedure. Imagine the following code:

-- Create a new stored procedure
CREATE PROCEDURE PopulateTempTable
AS
BEGIN
	-- Create a new temp table
	CREATE TABLE #TempTable
	(
		Col1 INT IDENTITY(1, 1),
		Col2 CHAR(4000),
		Col3 CHAR(4000)
	)

	-- Create a unique clustered index on the previous created temp table
	CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)

	-- Insert 10 dummy records
	DECLARE @i INT = 0
	WHILE (@i < 10)
	BEGIN
		INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
		SET @i += 1
	END
END
GO

Here you are creating through a DDL statement (CREATE UNIQUE CLUSTERED INDEX) an index, which means you are mixing DDL with DML statements. For that reason SQL Server is not able to cache your Temp Table. You can prove that behavior by tracking the performance counter Temp Tables Creation Rate from from the DMV sys.dm_os_performance_counters as in the following example:

DECLARE @table_counter_before_test BIGINT;
SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'

DECLARE @i INT = 0
WHILE (@i < 1000)
BEGIN
	EXEC PopulateTempTable
	SET @i += 1
END

DECLARE @table_counter_after_test BIGINT;
SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'

PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test)
GO

When you are running that code, SQL Server has to create 1000 individual Temp Tables, as you can see from the output window in SQL Server Management Studio:

It's very easy to overcome that problem by enforcing the UNIQUE CLUSTERED INDEX with the PRIMARY KEY constraint. In that way you are not mixing DDL with DML statements anymore and SQL Server is finally able to cache your Temp Table.

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
	-- Create a new temp table
	CREATE TABLE #TempTable
	(
		Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
		Col2 CHAR(4000),
		Col3 CHAR(4000)
	)

	-- Insert 10 dummy records
	DECLARE @i INT = 0
	WHILE (@i < 10)
	BEGIN
		INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
		SET @i += 1
	END
END
GO

When you rerun the code from above which tracks the relevant performance counter, you can now see that SQL Server creates the Temp Table only once and reuses it:

As a conclusion this also means that SQL Server is not able to cache Temp Tables when you create additional Non-Clustered Indexes, because you are then again mixing DDL with DML statements within your Stored Procedure.

But with SQL Server 2014 you can overcome this limitation, because you are now able to create indexes inline within your CREATE TABLE statement. Look at the following code:

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
	-- Create a new temp table
	CREATE TABLE #TempTable
	(
		Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
		Col2 CHAR(100) INDEX idx_Col2,
		Col3 CHAR(100) INDEX idx_Col3
	)

	-- Insert 10 dummy records
	DECLARE @i INT = 0
	WHILE (@i < 10)
	BEGIN
		INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
		SET @i += 1
	END
END
GO

As you can see I have created 2 additional Non-Clustered Indexes on the Temp Table directly during the creation of the Temp Table itself. This again means we are not mixing DDL with DML statements, and SQL Server is again able to cache and reuse your Temp Table:

That's a very nice addition and positive side effect by defining indexes inline on Temp Tables in SQL Server 2014.

What do you think about that new feature? Please feel free to leave a comment.

Thanks for reading

-Klaus