Temporary Tables In SQL Server – Know How to Create

SQL Server used the concept of temporary tables in SQL Server, by which we can perform our task in a great way. Temporary tables can be created at run time, which can perform all that operations, that a simple table can perform. But, its scope is limited because it depends on the type of the table and its scope is limited.

Types of temporary tables in SQL server

Basically two types of temporary tables are used in SQL Server, these are:

• Local temporary table
• Global temporary table

Local temporary table in SQL Server

A user can use a local temporary table in SQL Server for the current connection, and when he disconnects the SQL Server instance, these tables are automatically deleted. The name of these tables is started with a hash (“#”) sign.

Global temporary table in SQL Server

These tables are created like a permanent table and these can be accessed by any user and by any connection, once these are created by a connection. These tables cannot be deleted until all the connections have not been closed properly. The name of these tables starts with double hash (“##”).

How to create a temporary table in SQL Server?

Now, we will know, how we can create temporary tables in SQL Server. At first we will create a local temporary table.

Creating Local Temporary Table in SQL Server

To create a local temporary table in your SQL Server, use the below script:

After executing the above script a local temporary table will be created in the tempdb database. To view this table you can execute the below command.

Now, we can insert our records in this temporary table.

Once you have executed all the above commands and closed the query window, and open it again and insert a new record in this temporary table it will give you the below error message:

It happens, because the scope of a temporary table is limited, Local temporary table works, until the current connection has not closed.

Global Temporary Table

To create a global temporary table in your SQL Server, use the below script:

A global temporary table will be created in tempdb database, you can view this by below command:

Now, you can insert new records in it.

The global temporary tables can be viewed in all SQL Server connections. Once it created all the users can view it.

Storage Location of a Temporary Table

The SQL Server stored these temporary tables inside of a temporary folder of tempdb database.

You can see the below diagram where i have previously created some local temporary tables which is visible from the single hash(#), and also you can see the newly created global temporary table which is visible from the double hash(##).

Uses of Temporary Tables in SQL server

We can use temporary tables in following scenarios:

• When we are manipulating rows in a stored procedure.
• We can use this to manipulate the result set data, but at first we need to store it ons a temp table.
• We can also use it if we have a complex joins.

Conclusion:

In the above section we know about temporary tables In SQL Server and also know about its types. However, temporary tables can be easily created in SQL Server and used to manipulate the data inside of our database, but the misuse of temporary tables is not good for the database. because after all, these tables are stored in tempdb database of the SQL Server and this can be affect to some other processes of the database.