What is the Temp table in SQL Server

Question

What is the temp table in SQL Server and where is it store ?

Satyapriya Nayak · Answer

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 Jayaraman · Answer

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) )

Vulpes · Answer

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

What is the Temp table in SQL Server

Answers (3)