Temporary Tables in SQL Server 2005

About Temporary Tables in SQL Server 2005

1. Temporary Tables are useful in a way when we want to create a table for temporary processing.
2. We can create temporary table in a same way as normal table except temporary table has a pound sign or hash sign (#)  preceding its name.
3. Temporary tables are created in a tempdb
4. Temporary is tables are available only in a batch or procedure that creates it. Once the execution of stored procedure or batch is over they are deleted.
5. If you create a temporary table in a Query analyzer, it will be available until  the end of the session.
6. You can also drop temporary table within the batch.
7. Temporary tables are accessible only those connections which creates the Temporary table. In this way the scope for temporary table is very limited.
8. Temporary tables are created in tempdb with a unique name. The unique name is created on assigned table name + connection identifier. Assigned table name is a name given to temporary table while creating it plus connection identifier.
9. The length of the Temporary table name is 116 characters + last 12 characters makes the name unique.

Let’s look at a simple example.

–Temporary Table Example

/*

Please execute as a batch. Since this will be executed in a SQL Query Browser (Analyzer) temporary table will be available till the session exists. So you might get error if you create table with the same name again in the same session.Because the table already exists.

you can also drop temporary table..Follow the below example 

*/

–Create Temporary Table
CREATE TABLE #Temp1 (Empno int PRIMARY KEY)

–Inserting row into a temporary table
INSERT INTO #Temp1 VALUES (1001)

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Insert rows from other Table
INSERT INTO #Temp1 SELECT EMPNO from Test

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Using Temporary Table in a stored procedure

CREATE PROCEDURE TempTableTest (@NewEmpNo int)
AS
SET NOCOUNT ON

BEGIN
    –Create new Temporary Table
    CREATE TABLE #Temp2 (Empno int PRIMARY KEY)

    –Insert new Record
    INSERT INTO #Temp2 VALUES (@NewEmpNo)

    –Display new Inserted Record
    SELECT * FROM #Temp2
END

EXEC TempTableTest 2

Result

Happy Learning..I will be posting few more articles on Temporary Tables