Skip to content

November 2012

Swag

Coding on the Database Health Reports project, making notes in my Quest Software notebook, with a pencil from Redgate SQL in the City, wearing my… Read More »Swag

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.

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