SQL Temporary Table

SQL Temporary Table and Table variable

I want to discuss about SQL Temporary Table.

Temporary tables are created in tempdb. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.


The scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another.

Syntax :

And Drop Syntex:

Table Variables:

@table_variables can only be accessed within the batch and scope in which they are declared. #temp_tables are accessible within child batches (nested triggers, procedure, exec calls). #temp_tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).


Thank you reading

3 thoughts on “SQL Temporary Table and Table variable

Leave a Reply

Your email address will not be published. Required fields are marked *