SQL Server CTE vs Temp Table vs Table Variable Performance Test

Problem

In a previous article, SQL Server Temp Table vs Table Variable Performance Testing, we looked at SQL Server performance differences between using a temp table and a table variable for different DML operations. One of the comments suggested comparing these results to using a Common Table Expression (CTE) for similar operations. In this article we will go through a few simple SELECT queries in order to compare the performance in SQL Server using a temporary table, table variable and a CTE. We will compare the execution times of each query as well as the system resources needed to complete each operation.

Solution

While a CTE is a really good tool it does have some limitations as compared with a temporary table or a table variable. This biggest difference is that a CTE can only be used in the current query scope whereas a temporary table or table variable can exist for the entire duration of the session allowing you to perform many different DML operations against them. That said the CTE is still a really useful tool which can make your T-SQL code more readable as well as makes writing recursive queries much less complex as the CTE can reference itself.

Compare Temp Table, Table Variable and CTE

For this test we will go through an example using the Adventureworks2014 database and gather all the order header data for each customers most recent order.

Below is the T-SQL for each of our test query types.

-- CTE
WITH t (customerid, lastorderdate) AS 
 (SELECT customerid, max(orderdate) 
  FROM sales.SalesOrderHeader
  GROUP BY customerid)
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO
-- Temporary table
CREATE TABLE #temptable (customerid [int] NOT NULL PRIMARY KEY, lastorderdate [datetime] NULL);
INSERT INTO #temptable
SELECT customerid, max(orderdate) as lastorderdate 
FROM sales.SalesOrderHeader
GROUP BY customerid;
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN #temptable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
DROP TABLE #temptable
GO
-- Table variable
DECLARE @tablevariable TABLE (customerid [int] NOT NULL PRIMARY KEY, lastorderdate [datetime] NULL);
INSERT INTO @tablevariable
SELECT customerid, max(orderdate) as lastorderdate 
FROM sales.SalesOrderHeader
GROUP BY customerid;
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN @tablevariable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration. The CTE also uses less CPU than the other two options and performs fewer reads (significant fewer reads that the table variable query).

Another Example Comparing Performance of Temp Table, Table Variable and CTE

Let’s also test getting the most recent order header data for just a single customer to see if there is any difference in performance for a smaller data set.

Here are the updated queries which add a WHERE clause to each statement we tested above.

-- CTE
WITH t (customerid, lastorderdate) AS 
 (SELECT customerid, max(orderdate) 
  FROM sales.SalesOrderHeader
  WHERE customerid=27604 
  GROUP BY customerid)
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO
--Temp table
CREATE TABLE #temptable (customerid [int] NOT NULL PRIMARY KEY, lastorderdate [datetime] NULL);
INSERT INTO #temptable
SELECT customerid, max(orderdate) as lastorderdate 
FROM sales.SalesOrderHeader
WHERE customerid=27604
GROUP BY customerid;
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN #temptable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
DROP TABLE #temptable
GO
--Table variable
DECLARE @tablevariable TABLE (customerid [int] NOT NULL PRIMARY KEY, lastorderdate [datetime] NULL);
INSERT INTO @tablevariable
SELECT customerid, max(orderdate) as lastorderdate 
FROM sales.SalesOrderHeader
WHERE customerid=27604
GROUP BY customerid;
SELECT * 
FROM sales.salesorderheader soh
INNER JOIN @tablevariable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

Looking at these SQL Profiler results (averaged over 10 executions) we again see that the CTE actually gives us the best overall performance in terms of duration and resources used. The surprising thing in this case though is that the table variable outperformed the temporary table in all categories.

As mentioned at the start of the article, a CTE is not quite as versatile as temporary tables and table variables, but we’ve shown here that for individual queries that require temporary/staged data it can be a good option to improve performance. Remember though when dealing with query performance to always test with your own queries/data as there are many variables that can affect performance and different use cases could lead to another option giving better performance.

Next Steps

• More on information CTEs (Common Table Expressions)
  • How to use SQL Server CTEs to make your TSQL code readable by humans
  • Recursive Queries using CTEs in SQL Server
  • SQL Server CTEs Usage and Examples
  • SQL Server CTE and SML recursion example
• More information on temporary tables and table variables
  • Create SQL Server temporary tables with the correct collation
  • Improving SQL Server performance when using table variables
  • Differences between SQL Server temporary tables and table variables
  • Temporary tables and table variables

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017