A temporary (temp) table in SQL Server is a special table that cannot be stored permanently on the database server. This table keeps a subset of data from a regular table and can be reused multiple times in a particular session. We cannot store this table in the memory. Since this table exists temporarily on the current database server, it will be deleted automatically when the current session ends, or the user terminates the database connection. We can get the temporary tables in the system database “tempdb.”
Temporary tables are convenient when we have a bunch of records in a table and only need to deal with a small group of those records on a regular basis. In such cases, we can filter the data once and keep them in a temporary table rather than filtering the data multiple times to get the required data. After that, we can run our queries on that temporary table. Thus, the temporary table has a feature that enables us to achieve complex tasks quickly.
Creating Temporary Tables in SQL Server
We can create temporary tables in two ways:
SELECT INTO
CREATE TABLE StatementsLet us first create a table to demonstrate the temporary table in both ways. The following statement creates a table named ‘Student’ in the sample database:
CREATE TABLE Student
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(20) NOT NULL,
age INT NOT NULL,
total_marks INT NOT NULL
)
INSERT INTO Student
VALUES ('Jolly Evans', 'Female', 20, 520),
('Josh Butler', 'Male', 22, 645),
('Rose Huges', 'Female', 25, 610),
('Laura Bennet', 'Female', 18, 430),
('Alan Simmons', 'Male', 20, 500),
('Kate Huges', 'Female', 22, 600),
('Joseph Paul', 'Male', 18, 643),
('Antonio Butler', 'Male', 23, 513),
('Diego Bennet', 'Male', 21, 699),
('Elis Simmons', 'Female', 27, 540);We can verify the table using the SELECT statement. It displays the below table:
Temporary table using SELECT INTO Statement
The SELECT INTO statement is the simplest method to create a temporary table in SQL Server. We generally used this method to save intermediate results for repetitive use in the same session.
The following syntax illustrates how to create a temp table using the SELECT INTO statement:
SELECT select_list
INTO temporary_table_name
FROM regular_table_name
WHERE conditions;The name of a temp table always starts with a hash symbol (#). The hash sign tells that the SQL Server is dealing with a temp table. For example, the below query creates a temp table in the tempdb database with the help of a SELECT INTO statement. We can use this table only in a session that creates a temporary table.
SELECT name, age, gender
INTO #MaleStudents
FROM Student
WHERE gender = 'Male'As we can see in the above statement, it creates a temp table named “#MaleStudents” with three columns (name, age, and gender) derived from the SELECT statements. It populated the data from the ‘Student’ table containing all the male student record’s names, ages, and gender. We should not forget that the temp table name must have a hash (#) sign at the beginning of its name.
Once we execute the statement, we can see where the temporary table exists by accessing the SQL Server Management Studio using the following path: Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables. Here, we get the temp table name along with the hash sign. See the below figure:
As we can see in the image, the temp table also contains a sequence number at the end of the name. It indicates a unique identifier that the SQL server creates for each temporary table to distinguish the temporary tables. It is created because more than one database connection can have the same name for multiple temporary tables.
Now, we can perform SQL operations on the temporary tables in the connection where it is created. For example, if we want to get the details of all the male students, then we can execute the query as follows:
SELECT * FROM #MaleStudents; This statement will fetch the following records:
Temporary Table using CREATE TABLE Statement
The CREATE TABLE statement is the second method to create a temporary table. The below statement will create a temp table named #FemaleStudents. If we want to create the same table (For example, #MaleStudents), it is required to change the current database to a new connection.
Here is the query to create a temp table using CREATE TABLE statement:
CREATE TABLE #FemaleStudents
(
name VARCHAR(65),
age INT,
gender VARCHAR (10)
)As we can see in the statement, it is similar to creating normal tables. The only difference is that the temporary table’s name begins with a hash (#) sign.
Next, we will add records into this table, same as a regular table:
INSERT INTO #FemaleStudents
SELECT name, age, gender
FROM Student
WHERE gender = 'Female'Now, we can query the data within the current session as below:
If we try to query the above table in another connection, SQL Server will through the following error message:
SQL Server through this error because the temporary tables can only be accessed inside the session that generated them.
Types of Temporary Table
SQL Server categorizes the temporary table into two types:
- Local Temporary Table
- Global Temporary Table
Local Temporary Table
Local temporary tables are stored in tempdb and are visible only to the session that created them. These tables are automatically destroyed when we close the connection from the instance of SQL Server. We can define the local temporary table name with the prefix of the hash (#) sign. The temp table can be created with the same name in multiple connections.
We can use the following syntax to create a local temporary table in SQL Server:
CREATE TABLE #local_temp_table
(
column1 data_type,
column2 data_type,
?.
?.
column_n data_type
);We have already seen the above temporary table examples that explain the local temporary table also.
Global Temporary Table
Global temporary tables are stored in tempdb and are visible to all users and all sessions. These tables are automatically destroyed when all users that are referencing the table disconnect the connection or when the last session that uses them has been completed. We can define the global temporary table name with the prefix of double hash (#) sign.
We can use the following syntax to create a global temporary table in SQL Server:
CREATE TABLE ##global_temp_table
(
column1 data_type,
column2 data_type,
?.
?.
column_n data_type
);Example
The below example explains the creation of the global temporary table. Here, we have created a temp table named “##FemaleStudents” stored in the tempdb system database.
CREATE TABLE ##FemaleStudents
(
name VARCHAR(65),
age INT,
gender VARCHAR (10)
)
INSERT INTO ##FemaleStudents
SELECT name, age, gender
FROM Student
WHERE gender = 'Female'We can query this table as below:
How to drop temporary table?
SQL Server allows us to remove the temporary table in two ways
Automatic
ManualAutomatic Deletion
SQL Server automatically drops the temporary table when we will close the connection that created them. SQL Server will delete a global temp table when the connection that created them is closed, and the queries referencing this table from other connections have been completed.
Manual Deletion
We can use the DROP TABLE statement to remove the temporary table manually from the connection in which they are created. The syntax is given below:
DROP TEMPORARY TABLE #table_name; If we want to remove multiple temporary table, we need to provide comma-separated table names. See the below syntax:
DROP TABLE #table_name1, #table_name2; Temporary Tables vs. Regular Table
The following comparison chart explains the main differences between temporary and regular tables:
| Regular Table | Temporary Table |
|---|---|
| Regular tables are permanent tables that are stored as files on hard disks. | Temporary tables are stored in the tempdb system database, not at the physical locations. |
| Regular tables are slower in loading data because it stored in the hard disks and logged the modifications in the log file. | Temporary tables are faster in loading data because it stores in the tempdb, and modifications are not logged in the log file. |
| The system will drop each regular table manually. | The system can drop each temporary table implicitly. |
Leave a Reply