Published on

December 15, 2009

Understanding the Difference Between TempTable and TableVariable in SQL Server

Recently, we discussed the myth surrounding the use of table variables in SQL Server. Today, we will continue our exploration of this topic by examining the difference between TempTables and TableVariables, specifically in relation to transactions.

First, let’s recap the basics. Both TempTables and TableVariables have similar structures and are stored in the database. However, there are important distinctions to be aware of when it comes to their behavior within transactions.

Local variables, as we know, are unaffected by transactions due to their limited scope. This is why it is crucial to use variables with caution. To illustrate this point, let’s consider a quick example:

DECLARE @intVar INT
SET @intVar = 1
SELECT @intVar BeforeTransaction

BEGIN TRAN
SET @intVar = 2
ROLLBACK

SELECT @intVar AfterRollBackTran

In this example, we can see that the value of the local variable remains unchanged after the transaction is rolled back.

Now, let’s apply the same test to TempTables and TableVariables. If TableVariables are true variables, they should exhibit the same behavior. Consider the following example:

USE AdventureWorks

-- Create Temp Table and insert single row
CREATE TABLE #TempTable (Col1 VARCHAR(100))
INSERT INTO #TempTable (Col1) VALUES ('Temp Table - Outside Tran')

-- Create Table Variable and insert single row
DECLARE @TableVar TABLE (Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1) VALUES ('Table Var - Outside Tran')

-- Check the Values in tables
SELECT Col1 AS TempTable_BeforeTransaction FROM #TempTable;
SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar;

/* Insert additional row in trans
Rollback Transaction at the end */
BEGIN TRAN
-- Insert single row
INSERT INTO #TempTable (Col1) VALUES ('Temp Table - Inside Tran')

-- Insert single row
INSERT INTO @TableVar (Col1) VALUES ('Table Var - Inside Tran')

ROLLBACK

-- Check the Values in tables
SELECT Col1 AS TempTable_AfterTransaction FROM #TempTable;
SELECT Col1 AS TableVar_AfterTransaction FROM @TableVar;

From this example, we can observe that, just like any local variable, a table variable is not affected by a transaction. This is an important detail to note, as it is common to see developers using TempTables and TableVariables interchangeably without fully understanding their impact on transactions.

By understanding the difference between TempTables and TableVariables, you can make informed decisions when choosing the appropriate option for your specific scenario. TempTables are useful when you need to store large amounts of data or perform complex operations, while TableVariables are more suitable for smaller datasets or temporary calculations.

Remember, it is crucial to consider the impact of transactions on your chosen variable type. If you require transactional consistency, TempTables may be the better choice. However, if you need a lightweight option that won’t be affected by transactions, TableVariables are a viable alternative.

Thank you for reading! If you have any other tips or insights related to SQL Server, feel free to share them with us.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.