There are many cases where you will be using temp tables, and many of us are trying to seek for performance improvement. Fortunately with SQL 2016, we now have a way to do it via memory optimized tables!

There are different usages of temp tables, most commonly is table variables , global temp tables and local temp tables. I will break down into 3 different parts to discuss each use case and how memory optimized tables can help to improve performance, along with how to set it up with test results.

First off, let's take a look at table variable. No matter you using table variable in your code or within a stored proc, every time you declare a table variable, SQL server will need to allocate structure at run time and drop it after its lifespan. Let take a look on how it work in action.

Let's assume a simple case here, where we will need to declare a table variable with 3 columns, insert 3 records to it, then delete it off the table. Let create a table type for this purpose:

(ColA int not null, ColB varchar(10), ColC varchar(10));

Then we will run the below query 20 thousand times to simulate a heavy work load:

GO 20000

The above query took 27 seconds on my test machine. Let's do the same on the memory optimized table, again we will define the table type but this time we will set the table to be memory optimized, I assume you already knew how to set the DB to contain memory tables, so I won't go through it here.

with (MEMORY_OPTIMIZED=ON)

Once we setup the type, let run the same query again for 20 thousand times:

GO 20000

With memory optimized table valuable, the query took 2 seconds on the same machine. There is 13.5X performance gain by just a small change. Different server might have different performance gain. One thing to note is that this works in SQL 2014 as well, remember to test it and make sure it can suit your needs.

We will look at the other use cases in the following post, stay tune!