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.

Read More »Temp Table vs Table Variable vs CTE and the use of TEMPDB.