SQL Server TEMPORARY TABLE

A temporary SQL table also known as a temp table is created in the database only for a specific session to store the data temporarily which is needed for a short duration of time. In this article, we are going to learn about SQL Server temporary tables, their types, how to use them, and why they are important for storage management and improving query performances in the SQL Server. So deep dive into this article to learn about SQL temporary tables in detail.

SQL Server's Temporary Tables?

In SQL server temporary tables are very much similar to the normal permanent tables. The permanent tables created in the database remain in the database permanently until you drop them. However temporary tables get created in the TempDB database and are automatically deleted when they are no longer used. They play a crucial role in improving the efficiency of the database operations by reducing the overhead of storing the intermediate results permanently during an ongoing operation in the SQL server.

Why are Temporary Tables Important in SQL Servers?

Here are some of the key reasons why temporary tables are important in SQL servers:

• Session-Specific, Data Storage: The data stored in the temp table is only for the ongoing session or transaction. As soon as the session ends the data is cleared from the database storage which saves a lot of space for the user and reduces the memory consumption.

• Optimized Performance: For complex operations, the intermediate data is stored in the temp which improves the query execution speed for the SQL server. As a result, the queries are executed at a much faster rate.

• Data Isolation: The temporary tables are only visible and accessible within the session in which they were created. This ensures data isolation and integrity.

• Safe Testing Environment: The temporary tables allow testing the queries without affecting the actual tables of the user. The users can test their queries easily without rolling back the operations. The temporary tables ensures that no permanent data of the user is affected during any query execution.

Types Of Temporary Tables

In SQL there are mainly two types of temporary tables.

1. Local Temporary Tables.
   
2. Global Temporary Tables.

1. Local Temporary Tables

• These tables are only created for a specific session and are only accessible for that session only.

• They are automatically dropped when a particular session or transaction ends.

• A single # sign is used during their creation.

• Ideal for short term data storage.

• Use these tables when you don't want others to manipulate your data.

2. Global Temporary Tables

• These tables are also temporary tables but they are accessible by multiple sessions.

• They are not automatically dropped they persist in the database until they are manually dropped or the server is restarted.

• A double ## sign is used during their creation.

• Ideal for long term data storage.

• Use these tables when you want to share your data with multiple users or within multiple sessions.

How to Use the Temporary Tables in SQL Server

The syntax to create the temporary table is the same as the syntax we use to create the normal permanent tables in our databases but here we use # before the table name to create it. Following is the syntax to create temporary tables:

Create Table #tableName(Attribute 1, Attribute 2 .... )

So now let's create a temporary table for better understanding. Here we are creating a simple table authors with 2 columns consisting Id and Name of the author. Execute the following commands below to create the temporary table.

CREATE TABLE #indian_authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

Output:

Insert data into the temporary tables:

INSERT INTO #indian_authors VALUES (1, 'Rahul Sharma')
INSERT INTO #indian_authors VALUES  (2, 'Anita Patel')
INSERT INTO #indian_authors VALUES  (3, 'Deepak Kumar')
INSERT INTO #indian_authors VALUES (4, 'Priya Singh')
INSERT INTO #indian_authors VALUES (5, 'Amit Chauhan')

Fetch the data of the temp table using the following command:

Select * from #indian_authors

Output:

SQL Server Temporary Table:

From the above picture you can see that our temporary table indian_authors is stored inside the temporary database tempDb not in other databases where the permanent tables are stored.

Similarly, you can also create the Global temporary tables just use double hash ## in place of single hash # in your queries.

CREATE TABLE ##indian_authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

Note: Global temp tables will be visible to all the connections in the SQL, server and will only be destroyed when the server is restarted or a specific drop request for that table is made.

Conclusion

In the following article, we have learned about the SQL server's temporary tables. We have learned how they can help us to improve over database performance by optimizing the queries. These temporary tables are powerful tools for manipulating the data efficiently and enhancing the query performance in the database. The use case of local and global temporary tables completely depends on the user, if the user isn't to isolate the data from others then they must use the local temporary table. However, if they want to share the data of the temp table across multiple sessions or to multiple users then they can use the global temporary table. We hope this article has helped you to understand the temporary tables in the SQL server.

gaurav472

Follow

Improve

Article Tags :

• Geeks Premier League
• SQL Server
• Databases
• Geeks Premier League 2023