What are temporary Tables ?
Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. These are like other User table where we can store data but only for temporary purpose. These temporary tables data, structure and related objects like Indexes keys etc are automatically gets drop by SQL Server, once the user get disconnect from the Server.
These temporary tables are like other tables in SQL Server where you can
- Add/drop constraints except foreign key
- You can perform DDL statements (Alter, Drop)
- Create clustered and non-clustered indexes
- Use identity columns
- Use it in transaction and it support transaction
- Perform any DML operations (SELECT, INSERT, UPDATE, DELETE)
- Create the table with same name using different session; make sure constraint name must be different in the table.
All these temporary data is being saved on tempDB inside SQL Server.
But keep in mind, this every things is temporary. Table structure / data gets automatically dropped by SQL Server once the session comes to an end.
What is table Variable ?
Temporary tables are similar to temporary Tables but with following restrictions
- You can not create non-cluster index and statistics on table variable but you can create it on temporary table.
- You can not use DDL statement on table variable but you can use it on temporary table.
- Table variable doesn’t support transaction whereas temporary table supports.
All these temporary data is being saved on SQL Server Buffer (Memory). So it’s not at all advisable to have a table variable to store large number of records.
What is the difference between Local Temporary Table and Global Temporary Table ?
Temporary Tables There are two types of temporary tables:
Local Temporary Table: Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server
You can create local temporary table adding # sign again the table name
Global Temporary Table: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. You can create global temporary table adding ## sign again the table name
What is the scope of Table variable ?
The scope of a table variable is limited to the specific batch. Once the batch is over, variable will be de allocated and everything is gone. They cannot be explicitly dropped
What is the scope of Temporary Table ?
Local temporary table is limited to the specific spid (session/connection) once the SQL Server session ends, it’s removed by SQL Server
Global temporary table has vast scope, these tables can be accessed by multiple user during various sessions. These will be available until it is dropped or you restart the sql server instance.
What are benefits of Temporary Table ?
Temporary tables is great option for developer to store the temporary data and do manipulation after that. Moreover, Developers, don’t even need to bother about dropping after usage.
The best part is you can use this table like permanent table and create dependant objects like index and stats to improve performance for really large temp tables.
What are benefits of Table variable ?
- Transactions (Insert / Update / Delete) are not recorded for the table variables and thus they add performance over temporary tables.
- Table Variables are fast as they resides in memory thus reduces IO
What are disadvantages of Temporary Table ?
Temporary tables resides on tempdb and results too much pressure on tempdb and cause resource contention.
What are disadvantages of Table variable ?
Size restriction, These are fast as they are in memory but Table variables will be kept in memory up to certain size – if they go beyond that, they’re swapped out to disk in the tempdb database, too – just like temporary tables.
Why to use Temporary table over table variable ?
There is always going to be battle to justify, temporary tables vs temporary variables v/s CTE but for me, temporary table is great option over any other solution for large data ware house projects as we need to save millions of records in temporary tables and temporary tables gives us flexibility of
- Adding /dropping constraints except foreign key
- Can perform DDL statements (Alter, Drop)
- Can create clustered and non-clustered indexes
- Can use identity columns
- can use it in transaction and it support transaction
- can perform any DML operations (SELECT, INSERT, UPDATE, DELETE)
- can create the table with same name using different session; make sure constraint name must be different in the table.