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

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

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.

1 2 3 4 5 6 7 8 9 10

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.