Temporary tables in SQL Server

Temporary tables:

• Temporary tables are used to store and process intermediate results by using the same selection, update, and join capabilities of SQL Server tables. However, using temporary tables can adversely affect system performance
• There are two types of temporary tables: global and local.
• Temp tables can be created locally (#TableName) or globally (##TableName)
• SQL server appends a unique number at the end ‘temporary table’ object to identify it , in order to avoid confusion when two users create the same temporary table name.
• It works pretty quickly. It is cached in memory.
• We should drop the temporary table explicitly.
• We should recompile the stored procedures again and again, if we use temporary tables inside it. It avoids recompilations, always we need to create temporary table and create indexed for that.
• Temp tables allow for multiple indexes to be created
• Temp tables can be used throughout multiple batches.
• Temp tables can be used to hold the output of a stored procedure

Syntax:
CREATE TABLE
#table_name [column_name [n] datatype...]
INSERT INTO
#tablevariable_name
SELECT   * FROM table_name [where (condition)]
(SQL query statements: update or delete, drop)
Sample Code:

CREATE TABLE
  #Yaks
(
YakID int,
YakName char (30)
)

INSERT INTO
#TibetanYaks (YakID, YakName)
SELECT
YakID, YakName
FROM
dbo.Yaks
WHERE YakType = 'Tibetan'

Posted in: SQL Server Tutorial