How to Create Temporary Table and Table Variables in SQL Server

Temporary Table

The syntax to create a temporary is that table name is prefixed with ‘#’ (pound). By looking at the prefix ‘#’ the sql server understands that it is a temporary table. The following code demonstrates how to create a temporary table.

CREATE TABLE #TempUserInfo

(

UserID int,

Name varchar(100),

Category varchar(100),

Email varchar(50),

Address varchar(250),

Status int

);

To keep data in temporary table one has to use in this way.

INSERT INTO #TempUserInfo (UserID,Name, Category,Email,Address)

SELECT        UserID, Name,Category, Email, Address

FROM          UserInfo

WHERE        Status = 1

To drop temporary table use this syntax

DROP TABLE # TempUserInfo

Important points about temporary table:

• It is almost similar to normal table with very few exceptions.
• The scope of the temp table is the current connection session. It can be accessed from any where in same connection session.
• This table is automatically dropped when the connection session is closed.
• Different connection sessions creating temporary table with same name will create different copies of the temporary table with the same name. Actually internally the table name is appended with a unique number to support many connection sessions creating tables with same name. You can notice how the table name is uniquely maintained by sql server by running this query. select * from information_schema.tables where table_name like ‘%TempUserInfo%’
• Foreign key relations cannot be applied to temp tables.
• Optionally you may drop the table at the end of its use. It is a good practice to drop any temporary table after use.
• When you create a temporary table it will be created in tempdb. At the time of table creation the tempdb is locked and hence there is some overhead involved using this.
• If you have more than 100 rows generally use a temporary table otherwise use table variable.

Table Variable

In SQL Server 2000 or higher, one 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 syntax to define a table variable is similar to the syntax to normal table definition. DECLARE keyword is used in place of CREATE keyword. And table name is prefixed with ‘@’ as all tsql variables do. Example is as follows

DECLARE TABLE @TempUserVar

(

UserID int,

Name varchar(100),

Category varchar(100),

Email varchar(50),

Address varchar(250),

Status int

);

To keep data in temporary table one has to use in this way.

INSERT INTO @TempUserInfo (UserID,Name, Category,Email,Address)

SELECT        UserID, Name,Category, Email, Address

FROM          UserInfo

WHERE        Status = 1

Important points about table variable:

• Querying table variables is very fast as there are no disk reads needed.
• The scope the table variable is same as other tsql variables. i.e, within statements batch or sproc
• Table variables cannot be dropped as they are automatically disappeared when they reach out scope
• As explained above all the data in table variables is stored in server’s memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.
• 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.

Thanks
(Faisal Sikder)