This post discuss about Temporary table in SQL Server, Usage , Advantages and Disadvantages of using them.
What is Temporary Table? Temporary Tables are short-lived tables, only exists for the duration of a database session. When a database session terminates, its temporary tables are automatically destroyed. Temporary tables are only visible to the session that creates them, They remain invisible to other users. In fact several users can create Temporary tables under the exact the same name, but each user will only see his or her version of the table. |  |
Usage
Temporary tables are used widely in a situation where queries become more complex
1. Create the temporary table
2. Insert data into the newly created table
3. Selecting the data from the temporary table by holding a lock on temporary database until the transaction has completed.
4. Drop the temporary table.
A temporary table is created on disk, in the system database tempdb.
USE [sample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Temptables]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @count int;
SET @count = 1;
CREATE TABLE #temptable(id INT,name VARCHAR(50));
INSERT INTO #temptable (id,name)(SELECT id,name FROM Emp);
SELECT @count = COUNT(id) FROM #temptable;
PRINT @count;
DROP TABLE #temptable;
END
Advantages
One of the major advantage of temporary tables is the decrease the amount of locking and logging. It is because the current user is the only user that interacting with the table.
Table gets cleared up automatically when current procedure goes out of scope, but you should manually clean-up the data when you are done with it.
Temporary tables are ideal for holding intermediate data used by the current SQL session and it also creates a temporary index on temporary table.
Disadvantages
Because a temporary table is the session specific identifier that is tacked on to the name, you can only give it a name that is limited to 160 characters including the # sign.
You can find some useful information on Temporary Tables here