Temporary Query Items: Temporary Tables - Ramblings of a SQL Hillbilly

Note: This is part of a series on Temporary Query Items.

We’ve talked previously about what a Temporary Query Item is and how scope, indexing, statistics, TempDB, and memory can all affect your choice of TQI for your queries. It’s all been important discussion, if a little unsexy – go back and read it if you haven’t, or you may get a little lost. If you have (no really, read it first!), then you’re ready to walk through our first Temporary Query Item – Temporary Tables.

A Little History

Temporary Tables have been present in SQL Server since at least SQL Server 2000. You should be able to make use of them everywhere.

Location, Location, Location

Temporary Tables live in TempDB. This is not necessarily a performance problem, as even objects in TempDB can be cached in memory. However, if too much is going on in TempDB, this can cause a performance loss due to contention. If WAY too much is going on in TempDB, use of a temporary table can actually cause you to blow TempDB, which means your query will fail. Don’t cause your query to fail. Using a temporary table certainly doesn’t guarantee problems, but you do want to be careful.

Regarding location scope, temporary tables give you a little bit of flexibility. You can declare your temporary table as global or local. A global temporary table can be accessed from other connections than the creating one. A local temporary table can only be accessed from the creating connection, although it can be used and reused as long as the connection is open.

Global

Global temporary tables are named with a ## prefix. So, if I want a global temporary table to hold pickles, I would name it ##Pickles. Once created, this table will hang around as long as something is using it, and anyone can make use of it.

Local

Local temporary tables are named with a # prefix. My same temp table for holding delicious pickles would be named #Pickles. Once created, this table can only be accessed from the creating connection. They can create their own #Pickles table for THEIR delicious pickles, because behind the scenes SQL Server will postfix your local temporary table name with a long string of underscores and a hexadecimal number, and postfix theirs with a different long string of underscores and a hexadecimal number. BIG HAIR DEAL: Clustered indexes on local temp tables are global in scope. This means that two or more people attempt to create local temporary tables with identically named clustered indexes, the runner-up loses and cannot create the table.

How Long Does It Live?

Temporary tables present a fair amount of longevity. If SQL Server stops, then your temp table will go away. If you close the creating connection, the temp table will go away. If you drop the temp table, it will also go away. Otherwise, temporary tables will happily sit there forever occupying TempDB. This makes it one of the longest-lived TQIs, which is one of its biggest strengths.

I should note here that if a global temporary table is actively being referenced when dropped or the creating connection is closed, the table will actually persist until it is no longer being actively referenced. This is a little nicer than getting an error because the object suddenly doesn’t exist in the middle of your query. It could also mean that you insert into something that immediately stops existing, though.

Indexing

Another strength of temporary tables is that they can be indexed. Specifically, temporary tables can have both clustered and nonclustered indexes applied to them. They can also be created with a clustered index already on them. I’ve used this to my advantage before by making a temporary table with a copy of the data I want, then indexing it to fit my series of queries.

Copy That

When you create a temporary table based on an existing data set, it is created as a separate physical copy of the data. Modifications to the temp table do not affect the original data source. This can be either a strength or a weakness , depending on your purposes for using a TQI.

But Does It Blend?

The data contained within a temporary table is modifiable. You can insert, update, or delete records within. And you can dance if you want to.

As mentioned above, modifications to the temporary table does not automatically translate back to the original data used to populate it.

Show Me The Code!

Test Data Setup

I’ll be using a test table to do some demonstrations. You can create/populate it here:

create table dbo.JK_Temp_Stuff_Test_Table (
       myIntKey int PRIMARY KEY CLUSTERED,
       myVarchar varchar(10) not null
);

insert into dbo.JK_Temp_Stuff_Test_Table (myIntKey, myVarchar)
values (5, 'Wheeee');

create table dbo.JK_Temp_Stuff_Test_Table_2 (
       myIntKey int PRIMARY KEY CLUSTERED,
       myOtherVarchar varchar(10) not null
);

insert into dbo.JK_Temp_Stuff_Test_Table_2 (myIntKey, myOtherVarchar)
values (5, 'Waffle');

Creation

Temporary tables can be created in a very similar way to plain old tables. You can do a standard create statement:

create table ##JK_Test_Temp_Table (
       myIntKey int not null,
       myVarchar varchar(10) not null
CONSTRAINT [PK_JK_Test_Temp_Table] PRIMARY KEY CLUSTERED
(
  [myIntKey] ASC
)
);

As it turns out, you can also do SELECT INTO with a temp table. I’ll show that later.

Modification

Inserting into a temporary table is just like regular tables as well.

insert into ##JK_Test_Temp_Table (myIntKey, myVarchar)
values (1, 'A'),
       (2, 'B'),
       (3, 'A'),
       (4, 'C');

Temporary tables are also updateable:

update tgt
   set myVarchar = 'D'
  from ##JK_Test_Temp_Table tgt
 where myInt = 4;

select *
  from ##JK_Test_Temp_Table;

Indexing

You can create indexes on your temp table after the fact. Run the SELECT with the execution plan, create the index, then rerun the SELECT and note the change in execution plan. BIG HAIRY WARNING: If you attempt to create an index on a temp table with the execution plan enabled, SSMS will blow up.

-- Enable Execution Plan
select *
  from ##JK_Test_Temp_Table
where myVarchar = 'A';

-- Disable Execution Plan
create nonclustered index JK_Test_Temp_Table_SIDX_1 on ##JK_Test_Temp_Table(
       myVarchar asc
);

-- Enable Execution Plan
select *
  from ##JK_Test_Temp_Table
where myVarchar = 'A';

Global vs Local

So far, we’ve been using a global temporary table. Here, we’ll look at local temporary tables – run the CREATE and INSERT in one window, then run the SELECT in another:

create table #JK_Test_Temp_Table_2 (
       myIntKey int not null,
       myVarchar varchar(10) not null
CONSTRAINT [PK_JK_Test_Temp_Table_2] PRIMARY KEY CLUSTERED
(
  [myIntKey] ASC
)
);

insert into #JK_Test_Temp_Table_2 (myIntKey, myVarchar)
values (1, 'A'),
       (2, 'B'),
       (3, 'A'),
       (4, 'C');

select *
  from #JK_Test_Temp_Table_2
where myVarchar = 'A';

Longevity

The temporary table goes away when you close the creating connection. Close your original window, then run this:

select *
  from ##JK_Test_Temp_Table
where myVarchar = 'A';

Source Data Dependence

And finally, we can see modifying the data in the temporary table is independent of the data that created it. Note the SELECT INTO syntax:

select *
  into #JK_Test_Temp_Table_3
 from dbo.JK_Temp_Stuff_Test_Table;

update #JK_Test_Temp_Table_3
   set myVarchar = 'Whooo';

select * from #JK_Test_Temp_Table_3;
select * from dbo.JK_Temp_Stuff_Test_Table;

Cleanup

And here’s the cleanup code for this exercise:

drop table dbo.JK_Temp_Stuff_Test_Table;
drop table dbo.JK_Temp_Stuff_Test_Table_2;

Moving On

In this post, we’ve focused on some of the strengths and weaknesses of temporary tables. While this is not sufficient information to decide that a temporary table would be a more or less advantageous choice of Temporary Query Item in all cases, it should give you some ideas on how to make use of them in your queries. Next up, we’ll take a look at our next TQI: Table Variables.