Temporary Tables in SQL Server

It is possible to create two types of temporary tables in SQL Server.

• The temporary tables that are stored in the tempdb, if you are using the table in a script as opposed to a procedure, you will have to rememer to delete the table.
• The other type are called Temporary Table Variables. These only exist in memory and are automatically cleared when the procedure has finished.

Temporary Tables

Temporary tables are stored in the tempdb rather than in any SQL Server user database. When installing a database, it is imperative that use the right collation as the other databases otherwise you will encounter collation errors and may have to edit stored procedures if you are transferring or upgrading your servers.

Temporary table can be explicit, that is you use a Create Table or implicit, you can create them with via a Select statement. Its always best to explicitly create them so that you have control over the fields and sizes, otherwise SQL Server has to work out the fields. When using.

When using a temporary table outside a procedure, you will need to drop the table otherwise, the table will still exist. In procedures, temporary tables are always dropped automatically

Create Table #Tmp(ID int, Name varchar(20))

Insert into #Tmp
Select Id from Stars

All temporary tables are identifiable by the # prefix of the table. The other way of creating temporary tables is :-

Select Id, Name into #tmp from stars

Using the # table, you can apply a range of different commands, e.g. alter table, indexes etc... You are limited in what you can do with temporary table variables, you can only create a clustered index on variable table.

When using a temporary table in a script, be sure to drop the table otherwise it will stay. When a temporary table is used in a stored procedure, the table will be automatically dropped.

Temporary Table Variables

These are not as efficient as the form of table variables and should be used sparingly. You have limited functionality on temporary tables, you can only have one clustered index for example.

Table variables are great for use in functions where you are not able to update records but you can create temporary table variables. They persist for the length of the procedure or script. They are not stored on desk unless there are memory constraints.

Instead of using Create, you use the Declare statement to declare them like :-

Declare @Tmp Table (ID int identity, Name varchar(20))

If you were to convert the SQL in the previous article to using table variable, it would be extremely slow and inefficient.

Tags - SQL

Last Modified : June 2023