Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.

TempTables

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
-----------

TableVariables

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
-----------

Influenced Table Variable


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.

Advertisements