Introduction: Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. These tables can be created at run time and can do the all kinds of operations like insert, update, delete, selection, join and so on. One of the best features of a temporary table is you can store temporary data inside it and the temporary table will be automatically deleted when the current client session is terminated. These tables are created inside tempdb database.
Types of temporary table:
1) Local temp table: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#”) sign.
2) Global temp table: Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. 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.
Before we start creating temp table, we need to keep below points in mind.
- 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.
When to use Temp tables:
- When we are doing large number of row manipulation in stored procedure
- When we have complex join operation.
- 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.
How to create temporary tables:
1. Local Temporary Table
To create a local temporary table we will use “# symbol” so the syntax of the local temporary table is:
create table Table_Name; ( Column_Name [datatype](size), Column_Name [datatype](size),........ )
create table #Example_Table ( id int, Name varchar(20) )
2. Global Temporary Table
To create a global temporary we use “## symbol” so the syntax of the local temporary table is:
create table ##Table_Name ( Column_Name [datatype](size), Column_Name [datatype](size),........ )
create table ##Example_Table ( id int, Name varchar(20) )