Search content within the blog

Wednesday, April 22, 2009

Temporary tables and Table Variables in sqlserver

Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global.

Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed.

Both types of temporary tables are created in the system database tempdb.

Creating Temporary Tables

Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement. To make the table a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##).

-- Create a local temporary table using CREATE TABLE
CREATE TABLE #myTempTable
(
DummyField1 INT,
DummyField2 VARCHAR(20)
)

-- Create a local temporary table using SELECT..INTO
SELECT
age AS DummyField1,
lastname AS DummyField2
INTO #myTempTable
FROM DummyTable


Both of these samples create a local temporary table named #myTempTable with two fields DummyField1 and DummyField2.

To make these into global temporary tables, just replace (#) with (##)

CREATE TABLE ##myTempTable
(
DummyField1 INT,
DummyField2 VARCHAR(20)
)

SELECT
age AS DummyField1,
lastname AS DummyField2
INTO ##myTempTable
FROM DummyTable


Similarly, you can SELECT, INSERT, and UPDATE like any other table, but remember the prefix is part of the table name.

Deciding between Local and Global

When deciding which type of table to use ask yourself two questions. First, "Do I need this data to persist when I am done using it?" If so, I need a standard table, not a temporary table. Second, Do I need the data to be accessed outside of my single process?" This question can sometimes be a little tougher to figure out, so I have a simple suggestion. Make it a local temporary table for now, and if you find out you need a larger scope, change it later. In today's world there are plenty of tools to find and replace, so there's no reason to start with a global temporary table unless you know you will need it.


Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = 'Tibetan'

-- Do some stuff with the table

Table variables don't need to be dropped when you are done with them.
Which to Use

* If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
* If you need to create indexes on it then you must use a temporary table.
* When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

No comments:

Post a Comment