SQL Server, one of the most widely used relational database management systems, is equipped with a variety of features and functionalities for efficiently managing complex data operations. Temporary tables stand out among these features as a potent data manipulation tool, allowing developers to store and manipulate temporary data within the context of a session or transaction. In this article, we will discuss temporary tables in SQL Server, their benefits, and their various varieties.
What are Transient Tables?
Temporary tables are database objects that are created and used intermittently during a session or transaction. Temporary tables, unlike permanent tables, are not retained in the database schema and are dropped automatically at the conclusion of the session or transaction in which they were created. This makes them ideal for handling intermediate results or temporary data that must be processed or manipulated before being discarded.
The benefits of temporary tables
Temporary tables enable us to segregate and isolate temporary data within a particular session or transaction, preventing interference with permanent tables or other sessions. This makes them particularly useful in multi-user environments with concurrently running sessions.
- By storing intermediate results in temporary tables, it is possible to optimize complex queries and eliminate the need to repeatedly implement costly operations. Temporary tables can be indexed, which further improves query performance, particularly for large datasets.
- Temporary tables can simplify complex queries by dividing them into smaller, more manageable sections. We can divide a complicated operation into multiple stages, store intermediate results in temporary tables, and construct the final output gradually. This simplifies the creation of queries and enhances the code's legibility and maintainability.
- Local temporary tables, global temporary tables, and table variables are the three varieties of temporary tables supported by SQL Server. Each type has unique properties and application scenarios.
Temporary Local Tables
Local transient Tables in SQL Server are transient database objects that are created with a single hash sign (#) prefix and are only accessible within the session that generates them. These tables are dropped automatically when the session terminates or when they are no longer relevant. Local temporary tables allow for the storage and manipulation of session-specific transient data.
Here is an example of the creation and utilization 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, 'Peter', 'Scott', '2023-01-01');
INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Tom', 'Scott', '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, 'Peter', 'Scott', '2023-01-01'), (2, 'Tom', 'Scott', '2023-02-01'),
(3, 'Michael', 'Dawn', '2023-02-01'), (4, 'Lucas', 'Carty', '2023-02-01');
-- Query the temporary table
SELECT * FROM #TempEmployees;
-- Update data in the temporary table
UPDATE #TempEmployees
SET FirstName = 'Leo'
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, 'Peter', 'Scott', '2023-01-01');
INSERT INTO @TempTable (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Tom', 'Scott', '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, 'Peter', 'Scott', '2023-01-01'), (2, 'Tom', 'Scott', '2023-02-01'),
(3, 'Lucas', 'Carty', '2023-02-01');
-- Query the table variable
SELECT * FROM @TempTable;
-- Update data in the table variable
UPDATE @TempTable
SET FirstName = 'Leo'
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.
HostForLIFEASP.NET SQL Server 2022 Hosting
