Temporary Tables in SQL Server

Temp Table: 

1. Created in Temp database. Can treat as a normal table. 

2. Can be accessible outside the batch.

 

-- check object is exists in tempdb

IF OBJECT_ID('tempdb..#emptemp') IS NOT NULL 

  DROP TABLE #emptemp

CREATE TABLE #emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)  

GO  -- batch completed

SELECT * FROM #emptemp

INSERT INTO #emptemp VALUES ( 1, 'Scott','Newyork');  

GO  -- batch completed

SELECT * FROM #emptemp

 

3. Temp tables involves in Transactions, Logging, and Locking.

4. Can create Indexes on Temp tables.

5. Temp tables cannot be passed as parameters to procedures and functions.

6. We can perform schema modification on the Temp table run time.

7. Two types of temporary tables.

7.1. Local Temporary Tables: available in the session where they created. scope of these tables terminates when the session created is closed. Can create the same table in multiple sessions with the same name. 

       

CREATE TABLE #emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)

7.2. Global Temporary Tables: available for all sessions and all users. Scope of these table ends when the global temp table created session ends.

for example:

in session1 we created the below table, this table can be accessible in other sessions. If session1 terminates, we can not access this global temp table.

CREATE TABLE ##emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)

8. We cannot access local and global temporary tables in user-defined functions.