What's the difference between a temp table and table variable in SQL Server?

It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

SQL Server supports a few types of SQL Server temp tables that can be very helpful.

Before we proceed, if you want to follow along with any code samples, I suggest opening SQL Server Management Studio:

Local SQL temp tables

Local SQL Server temp tables are created using the pound symbol or “hashtag” followed by the table name. For example: #Table_name. SQL temp tables are created in the tempdb database. A local SQL Server temp table is only visible to the current session. It cannot be seen or used by processes or queries outside of the session it is declared in.

Here’s a quick example of taking a result set and putting it into a SQL Server temp table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

/*Insert Databases names into SQL Temp Table*/ BEGIN TRY DROP TABLE #DBRecovery END TRY BEGIN CATCH SELECT 1 END CATCH   SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]),     DBName = [name],     RecoveryModel = [recovery_model_desc] INTO #DBRecovery FROM sys.[databases] WHERE [recovery_model_desc] NOT IN ('Simple')

One of the most often used scenarios for SQL Server temp tables is within a loop of some sort. For example, you want to process data for a SQL statement and it you need a place to store items for your loop to read through. It provides a quick and efficient means to do so. See the code sample above, your loop can now reference the SQL Server temp table and process the records that meet the criteria of your goal.

Another reason to use SQL Server temp tables is you have some demanding processing to do in your sql statement. Let’s say that you create a join, and every time you need to pull records from that result set it has to process this join all over again. Why not just process this result set once and throw the records into a SQL temp table? Then you can have the rest of the sql statement refer to the SQL temp table name. Not only does this save on expensive query processing, but it may even make your code look a little cleaner.

There is one point that I want to make however. If the session that we’re working in has subsequent nested sessions, the SQL Server temp tables will be visible in sessions lower in the hierarchy, but not above in the hierarchy. Please allow me to visualize this.

In this quick diagram, a SQL temp table is created in Session 2. The sessions below it (sessions 3 and session 4) are able to see the SQL Server temp table. But Session 1, which is above session 2, will not be able to see the SQL Server temp table.

The SQL temp table is dropped or destroyed once the session disconnects. Many times you’ll see developers use the “DROP #Table_Name” command at the end of their statement just to clean up. But it is entirely up to you and what you’re trying to accomplish.

Also note, that in the event of name conflict (remember that SQL Server temp tables are created in the tempdb) SQL server will append a suffix to the end of the table name so that it is unique within the tempdb database. But this process is transparent to the developer/user. You can use the same name that you declared as it’s confined to that session.

Global SQL temp tables

Global SQL temp tables are useful when you want you want the result set visible to all other sessions. No need to setup permissions. Anyone can insert values, modify, or retrieve records from the table. Also note that anyone can DROP the table. Like Local SQL Server temp tables, they are dropped once the session disconnects and there are no longer any more references to the table. You can always use the “DROP” command to clean it up manually. Which is something that I would recommend.

To create a global SQL temp table, you simply use two pound symbols in front of the table name. Example: ##Global_Table_Name.

Table Variables

Table variables are created like any other variable, using the DECLARE statement. Many believe that table variables exist only in memory, but that is simply not true. They reside in the tempdb database much like local SQL Server temp tables. Also like local SQL temp tables, table variables are accessible only within the session that created them. However, unlike SQL temp tables the table variable is only accessible within the current batch. They are not visible outside of the batch, meaning the concept of session hierarchy can be somewhat ignored.

As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Otherwise a SQL Server temp table is useful when sifting through large amounts of data. So for most scripts you will most likely see the use of a SQL Server temp table as opposed to a table variable. Not to say that one is more useful than the other, it’s just you have to choose the right tool for the job.

Here is a quick example of setting up and using a table variable.

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE @TotalProduct AS TABLE (ProductID INT NOT NULL PRIMARY KEY,  Quantity INT NOT NULL)    INSERT INTO @TotalProduct          ( [ProductID], [Quantity] )  SELECT   A.[ProductID],   [Quantity] = SUM(B.Quantity) FROM dbo.Product AS A INNER JOIN dbo.SalesDetails AS B ON A.ProducitID = B.ProductID

We’ve created a table variable that will hold information regarding total quantities of a certain product sold. This is a very simplified example, and we wouldn’t use it if it contained a lot of rows. But if we were only looking at a few products this could really well. Once the table variable is populated you can then join this as a table to yet another table and gather whatever information you need. So there is a lot of flexibility and allows the developer to be quite creative.

Also, on a final note, in terms of transactions on table variables. If a developer rolls back a transaction which includes changes to the table variables, the changes made to the table variables within this particular transaction will remain intact. That is to say, other parts of this transaction in question will be rolled back, but anything referencing the table variable will not, unless that portion of your script is in error.