Temp Tables in Sql Server

Four kinds of temp tables

1. Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances

CREATE TABLE #CRSTYPES(COURSETYPEID TINYINT,COURSETYPENAME VARCHAR(32))

2. Table Variables:

A table variable is created in memory, and so performs slightly better than #temp tables. Table variables are automatically cleared when the procedure or function goes out of scope.

DECLARE @MYSCHEDULE TABLE(SCHEDULEID INT PRIMARY KEY)

3. Global Temp Table:

Global Temporary tables name starts with a double hash ("##").
Once this table has been created by a connection, like a permanent table it is then available to any user
by any connection. It can only be deleted once all connections have been closed.

CREATE TABLE ##CRSTYPES(COURSETYPEID TINYINT,COURSETYPENAME VARCHAR(32))

4. Common Table Expression (CTE) Varaible:

with exmp (COURSETYPEID, COURSETYPENAME) as
(
SELECT COURSETYPEID,COURSETYPENAME FROM COURSETYPES WHERE COURSETYPEID IN (17,18)
)
select * from exmp
select * from exmp WHERE COURSETYPEID IN (17)


Storage Location of Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb.
Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them effectively. There are few steps to be taken.

• Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
• When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements,create the table using DDL statement and use INSERT INTO to populate the temporary table.
• Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
• After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
• When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.
• Use the table variables instead of the temporary tables whenever possible.
• Try to avoid using temporary tables inside your stored procedure.
• Try avoiding using insensitive, static and keyset cursors whenever possible.
• Use multi-statement table-valued functions to eliminate temporary table usage for intermediate result processing.
• Try to avoid using temporary tables by rewriting your Transact-SQL statements to use only standard queries or stored procedures.
• Use the derived tables or correlated sub-queries instead of the temporary tables whenever possible.
• Use local temporary tables instead of SQL Server cursors.
• Avoid creation temporary tables from within a transaction.
• Avoid using global temporary tables.
• Consider creation a permanent table instead of using temporary tables.
• Because all temporary tables are stored in the tempdb database, consider spending some time on the tempdb database optimization.
• Set a reasonable size for the tempdb database and a reasonable autogrow increment.