What is the Temp table in SQL Server

Temporary table:
These are similar to normal tables and can perform almost all operations as of normal table, but these tables can be created at run time.

SO for eg, if you want to save few records from a table, inside a stored proc, you can save it in temp table.

Where it is stored:
Tempdb

Types of Temp tables:
1. Local temp table:
It is available only to current connection of the user. Will be deleted automatically when user disconnects. Should start with '#' sign.

CREATE TABLE #LocalUser( User_ID int, User_Name varchar(50)
)

2. Global temp table:
It is available to all connections in the server. Will be deleted only if all users disconnect. Should start with '##' sign.

CREATE TABLE ##GlobalUser(
User_ID int, User_Name varchar(50)
)

When to use:
1. Instead of cursors in stored proc
2. instead of complex joins in stored proc.
3. When manipulation of data cannot be used in single query.

Performance issues:
1. User should delete temporary tables when it is of no more use.
2. Temp table affects bad performances. so table variables are preferred than temp tables.

Table variable example:
Declare @TableVar Table(
User_id int,
User_Name varchar(50)
)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Hi Shubham,

Please refer the below links

http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

http://aartemiou.blogspot.in/2009/04/where-are-temporary-tables-stored-in.html

Thanks
Santhosh Kumar Jayaraman14y ago
Temporary table:
These are similar to normal tables and can perform almost all operations as of normal table, but these tables can be created at run time.

SO for eg, if you want to save few records from a table, inside a stored proc, you can save it in temp table.

Where it is stored:
Tempdb

Types of Temp tables:
1. Local temp table:
It is available only to current connection of the user. Will be deleted automatically when user disconnects. Should start with '#' sign.
```
CREATE TABLE #LocalUser( User_ID int, User_Name varchar(50)
)
```
2. Global temp table:
It is available to all connections in the server. Will be deleted only if all users disconnect. Should start with '##' sign.
```
CREATE TABLE ##GlobalUser(
User_ID int, User_Name varchar(50)
)
```
When to use:
1. Instead of cursors in stored proc
2. instead of complex joins in stored proc.
3. When manipulation of data cannot be used in single query.

Performance issues:
1. User should delete temporary tables when it is of no more use.
2. Temp table affects bad performances. so table variables are preferred than temp tables.

Table variable example:
Declare @TableVar Table(
User_id int,
User_Name varchar(50)
)
Vulpes14y ago
Check out this link:

http://geekswithblogs.net/DevJef/archive/2011/10/08/sql-server-temp-tables.aspx