How to create temporary tables or table variables?

There are two kinds of temp tables: temporary tables and table variables. Both of them are very similar in nature, but there are some fundemenal differences. Here is an example of table variable creation:

DECLARE @ArticlesTable TABLE(ArticleId INT)

Fill it up:

INSERT INTO @ArticlesTable

 SELECT dbo.cmsArticle.ArticleId

FROM   dbo.cmsArticle

WHERE dbo.cmsArticle.CreatedDate<'01/01/2008'

Use it:

DELETE FROM dbo.cmsArticle WHERE ArticleId IN (SELECT * FROM @ArticlesTable)


Table variables are simplified version of temp tables. Table variables can't be altered by data definition language statements, so you can't add indexes. Each table variable has single clustered index, so it can be accessed only using cluster index scan. This makes table variables ideal for small datasets, <100,000 rows. It is recommended to use table variables everywhere where you need temp table with no specific indexes. Table variables unlike other sql server types can't be input or output parameter of stored procedure, so their scope is limited by stored procedure or other batch of t-sql statements. Whenever you access table variable query optimizer don't need to do any optimization, since it always does cluster index scan. Limited scope and no optimization makes table variable transactions shorter than temporary tables involved transactions. You can define table variable only using DECLARE keyword. You can't create table variable using SELECT INTO construct.

Temporary tables can be created just like regular tables using CREATE TABLE construct, just prefix name with # sign.

CREATE TABLE #ArticlesTable (ArticleId INT)


Also you can use SELECT INTO construct, no need for CREATE TABLE then:

SELECT dbo.cmsArticle.ArticleId
INTO #ArticlesTable
FROM  dbo.cmsArticle
WHERE dbo.cmsArticle.CreatedDate<'01/01/2008'


Temporary tables scope is limited to sql server connection. Whenever connection is closed then temporary table would be deleted unless you do it before closing connection using DROP TABLE.

Posted on Tuesday, March 11, 2008 by | Comments (1) | Add Comment



Re:MS SQL 2005 Temporary Tables

Isn't this example for a table variable ?

Posted on 8/8/2008 11:25:29 AM by avatare #

New Comment

Your Name:
Email (for internal use only):
Code above:


Valid HTML5