Where are Table Variables Stored? – SQL in Sixty Seconds #095

A very popular question we will answer today Where are Table Variables Stored? Recently I have posted few videos on my YouTube Channel about SQL Server performance Tuning and I have received an email from my client of Comprehensive Database Performance Health Check. Let us see the answer to that question in today’s blog post.

A very popular (and incorrect) understanding is that Table Variables are stored in only memory and they do not exist on the disk. This is the reason they are faster and efficient. Actually, it is not true at all. Let us talk about both the misconceptions one at a time.

Here is a video which discusses about the popular myth and proves with the help of demonstration that table variables are created in the TempDB as well.

Myth 1: Table Variables after Always Faster

First, let say that Table Variables are not necessarily faster than any alternative to it like Temporary Tables. They both are very much different and both have different usage. I have used both of them successfully but they are not a SQL replacement of each other.

Myth 2: Table Variables are Only Memory Object

Honestly, in SQL Server everything actually passes through buffer cache so technically we can say that everything is in memory. However, the popular argument which we hear is that Table Variables are memory only object and they do not exist on the temp table. Honestly, it is not true. Table variables just like a temporary table are created in the TempDB and also gets allocated with space the same way.

Script from Video

-- Get Current Datetime
DECLARE @CurrentDateTime DATETIME = GETDATE()
-- Create Temp Table 
CREATE TABLE #TempTable (ID INT)
-- Create Memory Table
DECLARE @TableVariable TABLE(ID INT)
-- Check the Object Created in Tamp Database
SELECT * FROM tempdb.sys.objects
WHERE type = 'U' 
	AND create_date >= @CurrentDateTime;
-- Clean up
DROP TABLE #TempTable;

My Preference

As I mentioned earlier, Table Variables and Temporary Tables both have their unique usage and they can’t be replaced with each other. However, if you are looking for the option to store the data temporarily and want optimal performance, I suggest you go with the Temporary Tables as in most of the cases during my consultancy, I have found them efficient.

In the future, I will write a blog post comparing their performance.

Reference: Pinal Dave (http://blog.SQLAuthority.com)