Temp table VS Table variable

Most of the SQL Developers/DBA would have come across a situation where they need to store the temporary result sets. This is where Temp tables and Table variables come into effect and helps in storing the data sets in a temporary location.

Temp table:

Consider the below sample temp table which holds the information about companies.

CREATE TABLE #Tmp

(

CompanyId Int,

Name varchar (50),

Location varchar (50)

)

1. The temp table name always starts with # or ## and are created in the tempdb database. The # indicates that the temp table is a local temporary table i.e. table is accessible only by the particular connection of SQL Server which created it. The ## indicates that the temp table is a global temporary table i.e. the table is accessible from any connection. They are dropped automatically when the last session that uses them has completed.
2. Since the local temporary table is accessible only by the connection which created it, this helps in minimizing the locks.
3. We can create indexes, statistics in temp tables and hence performance can be improved.
4. We cannot have foreign key constraints on temp tables.
5. Causes recompilation within stored procedures.
6. Only undo information is logged in tempdb and not the redo information.
7. We can Rollback the transactions in temp table similar to a normal table but not in table variable.
8. Temp tables can be used in nested stored procedures.
9. The temp table names cannot exceed 116 characters whereas the permanent table can have 128 characters

The following example illustrates the transaction behavior in Temp tables:

--using temp tables where ROLLBACK happens

CREATE TABLE #Tmp

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

GO

INSERT INTO #Tmp

VALUES (1,'Deepak','Chennai')

GO

BEGIN TRAN

UPDATE #Tmp

SET Location='CH'

WHERE CompanyId=1

ROLLBACK TRAN

SELECT * FROM #Tmp

Table variables:

The following the syntax for table variables:

DECLARE @Tmp TABLE

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

1. Table variables are local to a stored procedure and hence cannot be used in nested stored procedures
2. We cannot create Nonclustered indexes in Table variables only Clustered index can be created by specifying them as constraints (Primary or Unique)                                                                                                                                                        DECLARE @Tmp TABLE (C1 int, C2 int, PRIMARY KEY (C1, C2))
3. Table variables store the contents in memory but not always. Under extreme memory pressure, the pages belonging to table variables will be moved to tempdb
4. We cannot Alter a table variable once its declared
5. We cannot create statistics in table variables
6. They cannot make use of multiple processors and hence Parallelism is not possible
7. Transactions cannot be rollbacked in Table variable

The following example illustrates the transaction behavior in table variables:

--using table variables where ROLLBACK NEVER happens

DECLARE @Tmp TABLE

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

INSERT INTO @Tmp

VALUES (1,'Deepak','Chennai')

BEGIN TRAN

UPDATE @Tmp

SET Location='CH'

WHERE CompanyId=1

ROLLBACK TRAN

SELECT * FROM @Tmp

I tried the following to check the performance perspective of table variables and temp tables. I could see that Temp tables are quite faster than table variables if we load numerous records. However with <10000 records being loaded, the table variables were much faster than temp tables.

I have a table named testmember with 1.5 million records.

--took 52 seconds to complete

SET STATISTICS TIME ON

DECLARE @Tmp TABLE

(

memberid          bigint,

name    nvarchar(100),

firstname           nvarchar(100),

emailaddress     nvarchar(100)

)

INSERT INTO @Tmp

SELECT memberid, name, firstname, emailaddress FROM testmember

WHERE memberid between 1 and 1000000

SELECT T.memberid, T.name, T.firstname, T.emailaddress

FROM @Tmp T INNER JOIN testmember M

ON T.memberid=M.memberid

where M.Memberid=1000

SET STATISTICS TIME OFF

--DBCC DROPCLEANBUFFERS

--took 45 seconds to complete

SET STATISTICS TIME ON

CREATE TABLE #Tmp

(

memberid          bigint,

name    nvarchar(100),

firstname           nvarchar(100),

emailaddress     nvarchar(100)

)

INSERT INTO #Tmp

SELECT memberid, name, firstname, emailaddress FROM testmember

WHERE memberid between 1 and 1000000

SELECT T.memberid, T.name, T.firstname, T.emailaddress

FROM #Tmp T INNER JOIN testmember M

ON T.memberid=M.memberid

where M.Memberid=1000

SET STATISTICS TIME OFF

Since we can create indexes, statistics etc there is still a scope for further improvement in performance in temp tables. In general there is no hard and fast rule, if there are <10K records we can opt for table variable else use temp tables but always test the query and then take a decision.

Limitations of Temp tables and Table variables:

1. There will be high load on the disk where tempdb resides if temp tables are used frequently and to a large extent and we have to keep an eye on the tempdb growth to ensure that it doesn’t become full and consume disk space
2. Table variables will perform poorly with large record set since index cannot be created other than primary key (Clustered Index)