SQL Server provides the concept of temporary table. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database, a separate database.
Here I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article.
Different Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
Local Temp Table
Global Temp Table
Local Temp Table:
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
Global Temp Table
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
Creating Temporary Table in SQL Server
The syntax given below is used to create a local Temp table in SQL Server:
CREATE TABLE #LocalTempTable(
ID int,
Name varchar(50),
Address varchar(50))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
insert into #LocalTempTable values ( 1, 'Ravi','India');
Now select records from that table:
select * from #LocalTempTable
After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:
Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.
This is because the scope of Local Temporary table is only bounded with the current connection of current user.
Global Temporary Table
The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:
CREATE TABLE ##NewGlobalTempTable(
ID int,
Name varchar(50),
Address varchar(50))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
insert into ##NewGlobalTempTable values ( 1, 'Bhushan','India');
Now select records from that table:
select * from ##NewGlobalTempTable
Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Storage Location of Temporary Table
Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.
Now, if we deeply look into the name of Local Temporary table names, a 'dash' is associated with each and every table name along with an ID.
SQL server does all this automatically, we do not need to worry about this; we need to only use the table name.
When to Use Temporary Tables?
Below are the scenarios where we can use temporary tables:
Points to Remember Before Using Temporary Tables
Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
Number of rows and columns need to be as minimum as needed.
Tables need to be deleted when they are done with their work.
Alternative: @Table Variable
@table_variables (stored in tempdb) 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).
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.
Declare @TempTableVariable TABLE(
ID int,
Name varchar(50),
Address varchar(50))
The below scripts are used to insert and read the records for Tablevariables:
insert into @TempTableVariable values ( 1, 'Ravi','India');
Now select records from that tablevariable:
select * from @TempTableVariable
When to Use Table Variable Over Temp Table
Table Variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.
Here I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article.
Different Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
Local Temp Table
Global Temp Table
Local Temp Table:
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
Global Temp Table
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
Creating Temporary Table in SQL Server
The syntax given below is used to create a local Temp table in SQL Server:
CREATE TABLE #LocalTempTable(
ID int,
Name varchar(50),
Address varchar(50))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
insert into #LocalTempTable values ( 1, 'Ravi','India');
Now select records from that table:
select * from #LocalTempTable
After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:
Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.
This is because the scope of Local Temporary table is only bounded with the current connection of current user.
Global Temporary Table
The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:
CREATE TABLE ##NewGlobalTempTable(
ID int,
Name varchar(50),
Address varchar(50))
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
insert into ##NewGlobalTempTable values ( 1, 'Bhushan','India');
Now select records from that table:
select * from ##NewGlobalTempTable
Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Storage Location of Temporary Table
Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.
Now, if we deeply look into the name of Local Temporary table names, a 'dash' is associated with each and every table name along with an ID.
SQL server does all this automatically, we do not need to worry about this; we need to only use the table name.
When to Use Temporary Tables?
Below are the scenarios where we can use temporary tables:
When we are doing large number of row manipulation in stored procedures.
This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
When we are having a complex join operation.
Points to Remember Before Using Temporary Tables
Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
Number of rows and columns need to be as minimum as needed.
Tables need to be deleted when they are done with their work.
Alternative: @Table Variable
@table_variables (stored in tempdb) 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).
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.
Declare @TempTableVariable TABLE(
ID int,
Name varchar(50),
Address varchar(50))
The below scripts are used to insert and read the records for Tablevariables:
insert into @TempTableVariable values ( 1, 'Ravi','India');
Now select records from that tablevariable:
select * from @TempTableVariable
When to Use Table Variable Over Temp Table
Table Variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.