SQL SERVER – Table Variable or Temp Table – Performance Comparison – INSERT

Earlier this week, I wrote two blog posts which are comparing performance between regular tables and temp tables. In this blog post, we will talk about Performance Comparison between Table Variable or Temp Table. Before you continue reading this blog post I suggest you read the following blog posts:

• SQL SERVER – Regular Table or Temp Table – TempDB Logging Explained
• SQL SERVER – Regular Table or Temp Table – A Quick Performance Comparison

Now let us take the same concept and demonstration forward in this blog post where we will see the difference between the table variable and temp table.

Test 1: Data Load in Table Variable

Let us run the following script and measure the time of total execution.

SET STATISTICS TIME ON
GO
USE SQLAuthority
GO
-- Create Table
DECLARE @FirstIndex TABLE (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- INSERT 1
INSERT INTO @FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

Here is the output of statistics TIME of the query ran:

SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 576 ms.

Test 2: Data Load in Temp Table

Let us run the following script and measure the time of total execution.

SET STATISTICS TIME ON
GO
USE SQLAuthority
GO
-- Create Table
CREATE TABLE #FirstIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- INSERT 1
INSERT INTO #FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
DROP TABLE #FirstIndex
GO

Here is the output of statistics TIME of the query ran:

SQL Server Execution Times:
CPU time = 531 ms, elapsed time = 534 ms.

Summary

When you are trying to load the data, the behavior of the Temp Table and Table variable is pretty much the same. The behavior of the table variable is very poor when we are trying to select the data from it. I will blog about it in the separate blog post.

Here are a few blog posts which are discussing the same topic in detail:

• SQL SERVER – How to Create Table Variable and Temporary Table?
• SQL SERVER – DROP Multiple Temp Tables Using Cursors on Azure
• SQL SERVER – Inside Temp table Object Creation – Part II
• SQL SERVER – Inside Temp Table Object Creation

If you have any question on this topic, please leave a comment and I will do my best to answer you.

Reference: Pinal Dave (https://blog.sqlauthority.com)