Temp Table vs Table Variable vs CTE and the use of TEMPDB.

For more information on Common Table Expessions and performance, take a look at my book at Amazon.com: Common Table Expressions Joes 2 Pros®: A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs

There are many reasons that a Temp Table, Table Variable or Common Table Expression can be a good solution, but there is also a lot of confusion around these.  Much of that confusion is around the use of TempDB.

For the purpose of this article I am going to use my standard Departments table from my CTE presentation. It is just a simple database to work with. Also, this is running on my local SQL Server with no other users connected.

USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'cte_demo')
BEGIN
 ALTER DATABASE [cte_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 DROP DATABASE [cte_demo];
END
CREATE DATABASE [cte_demo];
GO

USE [cte_demo];
-- create a table to use for CTE query demo
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

-- insert top level departments
insert into [Departments] (id, department, parent) values (1, 'Camping', null);
insert into [Departments] (id, department, parent) values (2, 'Cycle', null);
insert into [Departments] (id, department, parent) values (3, 'Snowsports', null);
insert into [Departments] (id, department, parent) values (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent) values (5, 'Tents', 1);
insert into [Departments] (id, department, parent) values (6, 'Backpacks', 1);
insert into [Departments] (id, department, parent) values (7, 'Sleeping Bags', 1);
insert into [Departments] (id, department, parent) values (8, 'Cooking', 1);

-- now some sub-departments for cycle
insert into [Departments] (id, department, parent) values (9, 'Bikes', 2);
insert into [Departments] (id, department, parent) values (10, 'Helmets', 2);
insert into [Departments] (id, department, parent) values (11, 'Locks', 2);

-- now some sub-departments for snowsports
insert into [Departments] (id, department, parent) values (12, 'Ski', 3);
insert into [Departments] (id, department, parent) values (13, 'Snowboard', 3);
insert into [Departments] (id, department, parent) values (14, 'Snowshoe', 3);

-- now some sub-departments for fitness
insert into [Departments] (id, department, parent) values (15, 'Running', 4);
insert into [Departments] (id, department, parent) values (16, 'Swimming', 4);
insert into [Departments] (id, department, parent) values (17, 'Yoga', 4);

Then to compare the difference on the TempDB usage, I am going to use an under-documented function called fn_dblog() which shows you a list of the items that are written to the transaction log in different scenarios.  I will also combine this with the CHECKPOINT command to clear the contents of the log for TempDB.

To try out out, run the following on a TEST or DEVELOPMENT database.  Don’t run this on your production system.

USE TempDB;
GO

SELECT *
 FROM fn_dblog(null, null);

CHECKPOINT;

SELECT *
 FROM fn_dblog(null, null);

Then take a look at the output which will look something like this.

The first time you select from fn_dblog() you may be many rows.  In my example fn_dblog() returned 171 rows, you may have many more.  But after running CHECKPOINT you only get 3 rows, and those are the 3 rows that you will normally get after running CHECKPOINT.

Now lets take a look at how Temp Tables, Table Variables and Common Table Expressions compare on their use of TempDB when doing similar work.

Temp Tables

Here is a sample of a temp table.

CREATE TABLE #deptTempTable (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #deptTempTable
SELECT id, department, parent
 FROM Departments;
SELECT * FROM #deptTempTable;

DROP TABLE #deptTempTable;

Now if we want to check the log before and after the temp table usage, here is how I would do it.

USE TempDB;
GO
CHECKPOINT;

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);
USE cte_demo;

CREATE TABLE #deptTempTable (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #deptTempTable
SELECT id, department, parent
 FROM Departments;
SELECT * FROM #deptTempTable;

DROP TABLE #deptTempTable;
GO

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

And you can see from the results this this temp table example we end up with 110 log entries for about 12KB of data written.

Table Variables

Now here is how we would do the same thing with a table variable.

DECLARE @deptTableVariable TABLE(
 id int,
 department VARCHAR (200),
 parent int
 );
INSERT INTO @deptTableVariable
SELECT id, department, parent
 FROM Departments;
SELECT * FROM @deptTableVariable;

Then we wrap it with the log checks to see how much TempDB is being used.

USE TempDB;
GO
CHECKPOINT;

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);
USE cte_demo;

DECLARE @deptTableVariable TABLE(
 id int,
 department VARCHAR (200),
 parent int
 );
INSERT INTO @deptTableVariable
SELECT id, department, parent
 FROM Departments;
SELECT * FROM @deptTableVariable;

GO

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

Producing the results below for log entries which show 80 log entries for about 8KB of data written to log.  Better than the temp table usage.

Common Table Expressions

Now lets see how the CTE’s perform.

;WITH deptCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM deptCTE q1;

With the log checking here is what we get.

USE TempDB;
GO
CHECKPOINT;

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);
USE cte_demo;

;WITH deptCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM deptCTE q1;

GO

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

which produces these results, which surprised me.

Which shows NO TempDB usage when using the CTE’s.  Keep in mind that TempDB usage is not a bad thing, the TempDB is there for a reason, and it should be used, but if you are running into contention or load issues on TempDB, you can get around these by using a CTE rather than a Temp Table, or Table Variable.

Recursive Common Table Expressions

Right after posting this article I received several questions on twitter about how the Recursive CTE is different than the non-Recursive CTE in the usage of TempDB.  Based on the comment made by Mr Ogden to the post, I wanted to be sure to get this one answered.

Here is the basic Recursive CTE from my Unleashing CTE’s presentation.

</pre>
-- Recursive CTE
;WITH DepartmentCTE(id, Department, Parent, Level) AS
( SELECT id, Department, parent, 0 as Level
 FROM Departments
 WHERE parent is NULL
 UNION ALL -- and now for the recursive part
 SELECT d.id, d.Department, d.parent,
 DepartmentCTE.Level + 1 as Level
 FROM Departments d
 INNER JOIN DepartmentCTE
 ON DepartmentCTE.id = d.parent)
SELECT *
 FROM DepartmentCTE
 ORDER BY parent;

and here it is with the TempDB checking:

USE TempDB;
GO
CHECKPOINT;
GO
SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

USE cte_demo;

-- Recursive CTE
;WITH DepartmentCTE(id, Department, Parent, Level) AS
( SELECT id, Department, parent, 0 as Level
 FROM Departments
 WHERE parent is NULL
 UNION ALL -- and now for the recursive part
 SELECT d.id, d.Department, d.parent,
 DepartmentCTE.Level + 1 as Level
 FROM Departments d
 INNER JOIN DepartmentCTE
 ON DepartmentCTE.id = d.parent)
SELECT *
 FROM DepartmentCTE
 ORDER BY parent;

GO

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

Which shows the following output.

Showing that there is no log written in TempDB with the Recursive CTE, and therefore TempDB is not used with this recursive CTE.   No in the them of Mythbusters, lets take it further.  I pulled in a few of my favorite recursive CTE examples from my Unleashing CTE’s presentation,  which include replacing a number table with a CTE, Calculating the Fibonacci Sequence with a CTE, and  Calculating Factorial with a CTE.  Lets see what that does to TempDB.

USE TempDB;
GO
CHECKPOINT;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

USE cte_demo;

-- Replacing a numbers table with a recursive CTE
WITH Numbers (N) AS
( SELECT 1
 UNION ALL
 SELECT 1 + N FROM Numbers
 WHERE N < 1000
)
 SELECT N
 FROM Numbers
 OPTION (MAXRECURSION 1000);

GO

-- Calculating the Fibonacci sequence
-- http://stevestedman.com/?p=1142
;WITH Fibonacci (PrevN, N) AS
( SELECT 0, 1
 UNION ALL
 SELECT N, PrevN + N
 FROM Fibonacci
 WHERE N < 1000000000
)
 SELECT PrevN as Fibo
 FROM Fibonacci
 OPTION (MAXRECURSION 0);

GO
-- Fibonacci with output as a csv
-- http://stevestedman.com/?p=320
WITH Fibonacci (PrevN, N) AS
( SELECT 0, 1
 UNION ALL
 SELECT N, PrevN + N
 FROM Fibonacci
 WHERE N < 1000000000
)
 SELECT Substring((SELECT cast(', ' as varchar(max)) +
 cast(PrevN as varchar(max))
 FROM Fibonacci
 FOR XML PATH('')),3,10000000) AS list;

GO

-- Calculating Factorials with a CTE : Step 5 - GO REALLY BIG
-- http://stevestedman.com/?p=1166
;WITH Factorial (N, Factorial) AS
( SELECT 1, cast(1 as NUMERIC(38,0)) -- Cast to NUMERIC to avoid overflow
 UNION ALL -- here is where it gets recursive
 SELECT N + 1, (N + 1) * Factorial
 FROM Factorial -- reference back to the CTE
 WHERE N < 33 -- abort when we get to 33!
)
 SELECT N, Factorial
 FROM Factorial;

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

When run produced the following output.

Which shows that nothing was written to TempDB with any of these 3 recursive CTEs.   Continuing on the Mythbusters way of breaking myths, lets go further.

CTE in a Function

Using samples from the same Unleashing CTE’s presentation, I decided to take a look at some of the CTEs that were wrapped in a function to see how they perform.  I am going to use the paging example from that presentation.

declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
name AS ColumnName,
ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
FROM sys.columns sc
)
SELECT *
FROM TablesAndColumns
WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
AND @pageNum * @pageSize ;

This is simply doing data paging through the sys.columns table.

USE TempDB;
GO
CHECKPOINT;
GO
SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

USE cte_demo;

-- CTE Example to get data paging
-- Assume that page size is 10 the first page would display the first 10 rows
-- the second page would display rows 11 to 20,
-- third page would display rows 21 to 30

declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
)
SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;
USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

When this was run it didn’t impact TempDB as shown here.

But then we take the same CTE and wrap it in a function like this….

CREATE PROCEDURE TablesAndColumnsPager
 @pageNum int,
 @pageSize int
AS
BEGIN
 SET NOCOUNT ON;

;WITH TablesAndColumns AS
 (
 SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
 )
 SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;
END

Then call it from inside our usual test case:

USE TempDB;
GO
CHECKPOINT;
GO
SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

USE cte_demo;

exec TablesAndColumnsPager 2, 10;

USE TempDB;
GO

SELECT count(1) as NumLogEntries, sum("log record length") as TotalLengthWritten
 FROM fn_dblog(null, null);

Still no TempDB usage.

Summary

What I have proven here is that if you are concerned with the usage of TempDB, for instance you are already experiencing TempDB problems, then CTE’s are a better solution than Temp Tables and Table Variables because they do not use TempDB, for either a normal CTE, a recursive CTE, or a CTE wrapped in a function.

With that being said, there are many other reasons that you may want to choose a temp table, or a table variable over a CTE, so be sure you understand the details when deciding on a Temp Table, Table Variable or CTE.

For more information on Common Table Expessions and performance, take a look at my book at Amazon.com: Common Table Expressions Joes 2 Pros®: A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!