Which one is better | Temp Table or Table Variable?

Download PDF – Which one is better | Temp Table or Table Variable?

Whether to use temp tables or table variables is an old interesting question since they were first introduced. In this post we will touch-base the differences between the table variable and Temp Tables.

Let’s check out the differences below then we will see which is better for performance.

Detail	Temp Tables	Table Variables
Storage Location	Stored in TempDB	Stored in TempDB
Indexes	Yes. Temp table allows both clustered and non-clustered indexes	1 PK/UK Only
Truncate Table	Yes	No
Alter Table	Yes	No
SELECT INTO	Yes	No
Types	Yes – Local and Global	No
Transaction participation	Yes	No
Log file writing	Yes	Yes
Insert with Exec	Yes	Yes
Statistics	Yes	No(1 Row always)
Parallelism participation	Yes	No
Recompiles	Yes	No

Temp tables are like normal SQL tables that are defined and stored in TempDB. The only difference between Temp table and a physical table is that temp table doesn’t allow foreign keys.

You can view the temp tables currently defined via SSMS by going to TempDB and Expanding Temp Tables.

As per my experience temp tables are better than table variables. The problem with the table variables is that query optimizer will generate bad/unpredictable query plans as they don’t have statistics on them.
If you check the estimated number of rows then it will always be 1.

DECLARE @tab AS TABLE ( ID INT )

INSERT INTO @tab
SELECT TOP 100 [CustomerID] FROM [dbo].[CustomerInfo]

SELECT ID FROM @tab

Output see below image
-----------

As a general rule of thumb across many SQL SERVER communities is that if you have 1000 rows or less then go for table variable otherwise go for temp table.
Well we can influence the query optimizer. One of the ways is given below-

DECLARE @x AS INT = 100

DECLARE @tab AS TABLE ( ID INT )
INSERT INTO @tab
SELECT TOP (@x) [CustomerID] FROM [dbo].[CustomerInfo]

Output
-----------

Table variables do not qualifying for parallelism that’s why they are better suited for small amounts of data.

Table Variables can write to the disk if threshold goes over a certain number of records.

Table variables do write to the log file and they can have non clustered indexes if associated with a ‘NONCLUSTERED UNIQUE’ or ‘NONCLUSTERED PRIMARY KEY’ constraint.