Temporary Table and Table Variables in SQL Server

  • February 8, 2017
  • Posted by: user
  • Category: Uncategorized
No Comments

Temporary Table

The following code demonstrates how to create a temporary table.
Syntax : [code:sql] CREATE TABLE #TempEmployee
(
employeeID int,
employeeName varchar(100)
);
[/code]

  • This table is automatically dropped when the connection session is closed.

  • 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.

 

Table Variables

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 as follows:

Syntax : [code:cf] DECLARE TABLE @TempEmployee
(
employeeID int,
employeeName varchar(100)
);
[/code]

  • Querying table variables is very fast as there are no disk reads needed.

  • Table variables cannot be dropped as they are automatically removed when they are out of scope

  • 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.

Note : 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.

More :
MDSN blog article: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx