Temporary table in SQL

There are two types of temporary tables are available in sql server

1) Local Temporary table

2) Global Temporary table

Local Temporary table: They are visible to only the current connection of the user.For each user it will create a temporary table along with a session value and delete from the system temp table once the inistance of the user to the particular table has gone.To add this table add a sign(#) before the table.

E.g:CREATE TABLE #tblTemp

(

col1 int,etc

)

2) Global Temporary table:They are visible to any user after they are created and delete when the user references the table disconnect from the sql server.To add this table add a sign(##) before the table.

E.g:CREATE TABLE ##tblTemp

(

col1 int,etc

)

Advantages :by using this we can avoid cursors and can do as a prameterized views.

Limitations :dont use properly it will reduce the overall perfromance.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. 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.

e.g :select a into #tblani from dbo.table_execplan --Dont use this one

e.g:

CREATE TABLE #tbl_ani1 (col1 nchar) INSERT #tbl_ani1 (col1) (select a from dbo.table_execplan)--try to use this one

• After you finish the using your temporary table, delete them. This will free the tempdb resources. 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.