Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the
.NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus
was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also
written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further
information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at
http://twitter.com/Aschenbrenner.

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:

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:

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.

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:

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.