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, but there are some fundamental 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 a 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 a single clustered index, so it can be accessed only using a 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 a temp table with no specific indexes. Table variables unlike other SQL server types can’t be input or an output parameter of a stored procedure, so their scope is limited by stored procedure or the other batch of T-SQL statements. Whenever you access table variable query optimizer doesn’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 a table variable only using DECLARE keyword. You can’t create a table variable using SELECT INTO construct.

Temporary tables can be created just like regular tables using CREATE TABLE construct, just prefix a name with a # 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 a connection is closed then a temporary table would be deleted unless you do it before closing a connection using DROP TABLE.