SQL SERVER – Temp Table vs Table Variable – Cardinality Estimation

It has been a while since I have been working with SQL Server 2019 with my clients to help them with performance-related issues while working on Comprehensive Database Performance Health Check. Recently, I had a very interesting conversation with one of my clients about Cardinality Estimation for Temp Table vs Table Variable. Let us discuss it today.

Short Story

The biggest culprit, I see often see at most of the server is either SQL Server Configuration or incorrect deployment. Often it is very easy to fix them and we can improve the performance of the server in just few minutes. This is the primary reason, why my Comprehensive Database Performance Health Check is just a maximum of 4 hours of service, as in most cases, we fix the problem in just a few minutes and spend the rest of the time fixing the performance offending queries.

While looking at the queries at my client, I noticed that they are using lots of table variables. Looking at their query patterns and workload, I gave them the suggestion that they need to change them to temporary tables as those will give them better performance.

The argument which I received from the team was that they prefer to use the table variables as they are memory only objects whereas temporary tables are a disk-based object. Actually, in real-world, temporary tables, as well as table variables both, are disk-based objects. You can read more about it in my earlier blog post: Where is Table Variable Created? – Interview Question of the Week #243.

The Cardinality of Table Variable

The real problem with the temporary table is there when we are reading more than a certain amount of the rows in the SQL Server. I have previously blogged about it here: SQL SERVER – Table Variables or Temp Tables – Performance Comparison – SELECT.

In earlier versions of SQL Server, no matter how many rows you have stored in the table variable, the table variable always reported that it contains only 1 row and based on the estimation of the 1 row, it created the execution plan. However, in SQL Server 2019, there is a bit of improvement in this logic. SQL Server 2019, now estimates 100 rows instead of 1 row. This means if you are storing around 100 rows in the table variable, your queries will now give better estimation and eventually build a better execution plan.

However, if you are going to store more than 100 rows in the table variable in SQL Server, once again you walk into the same performance problems which you used to face in the earlier version of SQL Server. Let us see a very quick demonstration of the behavior of the SQL Server table variable in the earlier version of SQL Server and the latest version of SQL Server.

Code to Execute – Cardinality Estimation

Here is the code which you can execute to check the cardinality issue of temporary table and table variable. Please note that you should enable the execution plan in SSMS.

Temporary Tables:

-- Enable Execution Plan (CTRL + M)
-- Creating a temporary table
CREATE TABLE #TempTableExample (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- Insert data into temporary table
INSERT INTO #TempTableExample (ID,FirstName,LastName,City)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Mike'+ CAST(ROW_NUMBER() OVER (ORDER BY a.name)%1000 AS VARCHAR),
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;
-- Writing a simple query
SELECT *
FROM #TempTableExample
WHERE City = 'Houston';
-- Clean up
DROP TABLE #TempTableExample
GO

Table Variables:

-- Enable Execution Plan (CTRL + M)
-- Creating a table variable
DECLARE @TempTableExample TABLE (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- Insert data into table variable
INSERT INTO @TempTableExample (ID,FirstName,LastName,City)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Mike'+ CAST(ROW_NUMBER() OVER (ORDER BY a.name)%1000 AS VARCHAR),
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;
-- Writing a simple query
SELECT *
FROM @TempTableExample
WHERE City = 'Houston';
GO

Now let us run the above code with SQL Server 2019 and right after that for SQL Server 2017 and compare the execution plan of the select statement.

SQL Server 2017 and earlier

You can see in the SQL Server 2017. When temporary tables are estimating rows to read correctly, for the table variable the estimated row is just 1 and that eventually leads to an incorrect execution plan.

SQL Server 2019

You can see in the SQL Server 2019. When temporary tables are estimating rows to read correctly, for the table variable the estimated row is just 100 and that eventually leads to an incorrect execution plan. However, if your table variable contains up to 100 rows, you are good at it. This is an improvement in SQL Server 2019 in Cardinality Estimation.

Final Verdict – Cardinality Estimation

In most cases (there are always some exceptions), Temporary tables work better than table variables and I prefer to use them for optimal performance of my server.

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