The debate whether to use temp tables or variable tables is an old debate and we usually ask which one is better and when we use temp tables or variable tables . In this article, I will show you the differences between the two and I hop you will have the appropriate approach in your situation. In SQL Server, we preface a temp table with sign # and a variable table with sign @

Temp tables are defined and stored in TempDB and the only difference between them and a permanent table is they are not allowed to have foreign keys. Let see the below table for the differences between temp tables and variable tables

Comparation Factor #Temp Tables @Variable Tables
1 Scope They are either #local defined (within session user)  or ##global defined (within session database). Can only be defined locally yet their scope is limited to the procedure or the batch which they are called.
2 Stored in TempDB YES YES
3 Writes to Log File YES YES
4 Can Qualify for Parallelism YES NO
5 Locking YES YES but there is no need to hold these locks once the statement has completed as they do not participate in any surrounding user transaction
6 Allows creation of statistics YES NO
7 Does not affect recompilation NO YES
8 Allows nonclustered indexes YES MAYBE if it’s a part of the primary key constraint
9 Allows clustered indexes YES We can define clustered indexes in variable tables within PRIMARY KEYS
10 Can perform SELECT INTO YES. YES
11 Can access in nested stored procedures YES NO
12 Can use in user defined functions NO YES
13 Can insert from EXEC YES YES
14 Allows TRUNCATE YES NO
15 Allows ALTER YES NO
16 Performance A good choice in case we need to process large data transfers because of parallelism, statistic, non-clustered indexes. In case, we only process a small data (<10K rows) may be @variable tables is simpler method

 

Now, I will demonstrate example for the differences:

Stored in TempDB

 /* Check the difference between Temp Table and Memory Tables */
— Get Current Session ID
SELECT @@SPID AS Current_SessionID
— Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
— Create Temp Table and insert three thousand rows
CREATE TABLE #TempTable (Col1 INT)
INSERT INTO #TempTable (Col1)
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
— Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
— Create Table Variable and insert three thousand rows
DECLARE @temp TABLE(Col1 INT)
INSERT INTO @temp (Col1)
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
— Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
— Clean up
DROP TABLE #TempTable
GO

 

Advertisements

One thought on “Compare a temporary table and a variable table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s