Types of Temporary Tables in SQL Server

Introduction

SQL Server, one of the most widely used relational database management systems, has many features and functionalities to handle complex data operations efficiently. Among these features, temporary tables stand out as a powerful tool for data manipulation, allowing developers to store and process temporary data within the context of a session or transaction. In this article, we will explore the concept of temporary tables in SQL Server, their advantages, and their types.

What are Temporary Tables?

Temporary tables, as the name suggests, are database objects created and used temporarily within a session or a transaction. Unlike permanent tables, temporary tables are not stored in the database schema and are automatically dropped at the end of the session or transaction in which they were created. This makes them ideal for handling intermediate results or temporary data that must be manipulated or processed before being discarded.

Advantages of Temporary Tables

Data Segregation: Temporary tables allow us to segregate and isolate temporary data within a specific session or transaction, preventing interference with permanent tables or other sessions. This makes them particularly useful in multi-user environments where multiple sessions may run concurrently.
Performance Optimization: By storing intermediate results in temporary tables, we can optimize complex queries and avoid the need to repeatedly execute expensive operations. Temporary tables can be indexed, which further enhances query performance, especially when dealing with large datasets.
Simplified Complex Queries: Temporary tables can break down complex queries into smaller, manageable parts. We can divide a complex operation into multiple steps, store intermediate results in temporary tables, and gradually build the final result. This simplifies query development and improves code readability and maintainability.

In SQL Server, three types of temporary tables are available: local temporary tables, global temporary tables, and table variables. Each type has its own characteristics and usage scenarios.

Local Temporary Tables

Local Temporary Tables in SQL Server are temporary database objects created with a single hash sign (#) prefix and accessible only within the session that creates them. These tables are automatically dropped when the session ends or when they go out of scope. Local temporary tables provide a means to store and manipulate temporary data within a specific session.

Here's an example that demonstrates the creation and usage of a local temporary table:

CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

-- Insert data into the temporary table
INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'Amit', 'Mohanty', '2023-01-01');

INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Asit', 'Mohanty', '2023-02-01');

In the above example, we create a local temporary table named #TempEmployees with columns: EmployeeID, FirstName, LastName, and HireDate. The INSERT INTO statement allows us to insert rows into the temporary table. In this example, we insert two rows with employee details. We can perform various operations on the local temporary table, such as querying the data. After we have finished working with the local temporary table, it's considered good practice to drop it explicitly to free up system resources:

DROP TABLE #TempEmployees;

The DROP TABLE statement removes the temporary table from the database. Remember that local temporary tables are automatically dropped when the session ends or when they go out of scope. Therefore, you don't need to worry about explicitly dropping them at the end of the session, but it's a good practice to drop them explicitly to free up system resources.

It's important to note that local temporary tables are only accessible within the session that created them. If we try to access a local temporary table from a different session or transaction, we will encounter an error. This isolation ensures that temporary data is specific to the session and does not interfere with other sessions or permanent tables.

Here's an example of how to use local temporary tables:

-- Create a local temporary table
CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

-- Insert data into the temporary table
INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'Amit', 'Mohanty', '2023-01-01'), (2, 'Asit', 'Mohanty', '2023-02-01'),
(3, 'Abinash', 'Hota', '2023-02-01'), (4, 'Swarup', 'Pradhan', '2023-02-01');

-- Query the temporary table
SELECT * FROM #TempEmployees;

-- Update data in the temporary table
UPDATE #TempEmployees
   SET FirstName = 'Deepak'
 WHERE EmployeeID = 1;

-- Delete data from the temporary table
DELETE FROM #TempEmployees WHERE EmployeeID = 3;

-- Query the temporary table after modifications
SELECT * FROM #TempEmployees;

-- Drop the temporary table at the end of the session
DROP TABLE #TempEmployees;

Global Temporary Tables

Global Temporary Tables in SQL Server are temporary database objects created with a double hash sign (##) prefix. Unlike local temporary tables, global temporary tables can be accessed by multiple sessions. These tables are dropped when the last session referencing them is closed. Global temporary tables are useful when you need to share temporary data across multiple sessions or when you want the data to persist beyond the scope of a single session.

Here's an example that demonstrates the creation and usage of a global temporary table.

CREATE TABLE ##TempProducts (
    ProductID INT,
    ProductName VARCHAR(50),
    Price DECIMAL(10,2)
);

-- Insert data into the global temporary table
INSERT INTO ##TempProducts (ProductID, ProductName, Price)
VALUES (1, 'Product A', 10.99);

INSERT INTO ##TempProducts (ProductID, ProductName, Price)
VALUES (2, 'Product B', 19.69);

In the above example, we create a global temporary table named ##TempProducts with the following columns: ProductID, ProductName, and Price. The INSERT INTO statement allows us to insert rows into the global temporary table. Similar to local temporary tables, we can perform various operations on the global temporary table, such as querying the data, and it's considered good practice to explicitly drop the global temporary table once we have finished working with it.

DROP TABLE ##TempProducts;

The DROP TABLE statement removes the global temporary table from the database.

Unlike local temporary tables, global temporary tables are accessible by multiple sessions. Each session referencing the global temporary table can perform operations like inserting, updating, or querying data. However, once the last session that references the global temporary table is closed, the table is automatically dropped.

Global temporary tables are beneficial when we need to share temporary data across multiple sessions or when you want the data to persist beyond the scope of a single session. They can be used for scenarios where temporary data needs to be shared or synchronized across different sessions.

Here's an example of how to use global temporary tables:

-- Create a global temporary table
CREATE TABLE ##TempProducts (
    ProductID INT,
    ProductName VARCHAR(50),
    Price DECIMAL(10,2)
);

-- Insert data into the temporary table
INSERT INTO ##TempProducts (ProductID, ProductName, Price)
VALUES (1, 'Product A', 10.99), (2, 'Product B', 19.69),
(3, 'Product C', 13.99), (44, 'Product D', 29.99);

-- Query the temporary table
SELECT * FROM ##TempProducts;

-- Update data in the temporary table
UPDATE ##TempProducts
   SET ProductName = 'Product AA'
 WHERE ProductID = 1;

-- Delete data from the temporary table
DELETE FROM ##TempProducts WHERE ProductID = 3;

-- Query the temporary table after modifications
SELECT * FROM ##TempProducts;

-- Drop the temporary table explicitly
DROP TABLE ##TempProducts;

Table Variables

Table variables in SQL Server are variables that can hold a set of data similar to a regular table. They are declared using the DECLARE statement and have a similar syntax to regular tables. Table variables exist only in memory and have a limited scope within a batch, stored procedure, or function. They are automatically dropped when the batch, procedure, or function finishes execution. Table variables are commonly used for storing and manipulating small result sets or as parameters in user-defined functions.

Here's an example that demonstrates the declaration and usage of a table variable.

DECLARE @TempTable TABLE (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

-- Insert data into the table variable
INSERT INTO @TempTable (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'Amit', 'Mohanty', '2023-01-01');

INSERT INTO @TempTable (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Asit', 'Mohanty', '2023-02-01');

In the above example, we declare a table variable named @TempTable with columns: EmployeeID, FirstName, LastName, and HireDate. Table Variables automatically drop when the batch, procedure, or function finishes execution. We don't need to explicitly drop it.

Table variables have certain limitations compared to temporary tables. They cannot be indexed, have constraints, or participate in transactions. They are typically used for smaller result sets or within a limited scope where the data volume is not significant. Table variables are often used within stored procedures or functions when there is a need to store intermediate results or perform calculations on a small data set.

Here's an example of how to use table variables.

-- Declare a table variable
DECLARE @TempTable TABLE (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

-- Insert data into the table variable
INSERT INTO @TempTable (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'Amit', 'Mohanty', '2023-01-01'), (2, 'Asit', 'Mohanty', '2023-02-01'),
(3, 'Swarup', 'Pradhan', '2023-02-01');

-- Query the table variable
SELECT * FROM @TempTable;

-- Update data in the table variable
UPDATE @TempTable
   SET FirstName = 'Deepak'
 WHERE EmployeeID = 1;

-- Delete data from the table variable
DELETE FROM @TempTable WHERE EmployeeID = 3;

-- Query the table variable after modifications
SELECT * FROM @TempTable;

Conclusion

Temporary tables in SQL Server provide a flexible and efficient way to handle temporary data within a session or transaction. They offer benefits such as data segregation, performance optimization, and simplified query development. Choosing the appropriate type of temporary table depends on the specific requirements of your application. If we need temporary data that are session-specific, local temporary tables are suitable. If we require temporary data that can be shared across sessions or that persists beyond a single session, global temporary tables can fulfill those needs. Table variables are lightweight for small result sets or within a limited scope.