In SQL Server, a temporary table is a unique kind of table that is used to hold data for a short time. When it is no longer required, it is automatically removed after existing for the duration of a batch or database session. The tempdb system database contains temporary tables.
Types of Temporary Tables
SQL Server supports two types of temporary tables.
- Local Temporary Table (#)
- Only visible to the session or connection that created it.
- Automatically deleted when the session ends.
- Global Temporary Table (##)
- Visible to all sessions and connections.
- Deleted only when the last session referencing it is closed.
Procedure for utilizing a temporary table
Step 1. Syntax for Creating a Temporary Table.
CREATE TABLE #TempTableName
(
Column1 DataType PRIMARY KEY, -- Example of a primary key
Column2 DataType,
Column3 DataType
);
Step 2. Example of Using a Temporary Table in a Stored Procedure.
CREATE PROCEDURE StudentDetailsTempTable
AS
BEGIN
-- Step 1: Create a temporary table
CREATE TABLE #StudentDetails
(
StudentID INT PRIMARY KEY,
-- Primary key example
StudentName NVARCHAR(100),
Course NVARCHAR(100),
Fees DECIMAL(18,2)
);
-- Step 2: Insert data into the temporary table
INSERT INTO #StudentDetails (StudentID, StudentName, Course, Fees)
VALUES
(1, 'Peter', 'MBA-IT', 160000.00),
(2, 'Leon', 'MBA-Economics', 180000.00),
(3, 'Alex', 'Master in technology in cs', 150000.00);
-- Step 3: Select data from the temporary table
SELECT * FROM #StudentDetails;
-- Step 4: Temporary table will be dropped automatically after procedure ends
END;
Notes
- Scope: A temporary table established within a stored procedure is confined to that specific procedure and is automatically removed upon the completion of the procedure's execution.
- Multiple Sessions: Temporary tables that begin with a # symbol are restricted to the session in which they were created. For access across different sessions, a global temporary table can be created using ##; however, local tables are generally considered safer for use within stored procedures.
When to Utilize Temporary Tables?
- Intermediate Results: Temporary tables are advantageous for storing intermediate results of queries temporarily within a stored procedure.
- Data Transformation: They serve the purpose of manipulating or aggregating data prior to delivering the final result set.
- Performance: In certain scenarios, the use of temporary tables can enhance performance, particularly when managing complex joins or aggregations involving large datasets.
Process for utilizing a Table variable
A table variable in SQL Server is a distinct category of variable designed to hold a temporary collection of data organized in a table structure. It is defined through the DECLARE statement utilizing the TABLE data type. While table variables share similarities with temporary tables, they exhibit notable differences in terms of scope, performance, and application.Limitations
- Prohibition of DDL Operations: DDL commands such as ALTER or DROP are not permitted on table variables.
- Restriction on Explicit Indexes: Only indexes that are established through constraints are permissible.
- Inefficiency with Large Data Sets: Table variables are not as effective as temporary tables when managing large volumes of data.
Step 1. To create a table variable, the appropriate syntax involves using the DECLARE statement in conjunction with the TABLE. Syntax for Declaring a Table Variable.
DECLARE @TableName TABLE
(
Column1 DataType PRIMARY KEY, -- Primary key example
Column2 DataType,
Column3 DataType
);
Step 2. Defining and Utilizing a Table Variable.
CREATE PROCEDURE StudentDetailsTableVariable
AS
BEGIN
DECLARE @StudentDetailsTable TABLE
(
StudentID INT PRIMARY KEY, -- Primary key example
StudentName NVARCHAR(100),
Course NVARCHAR(100),
Fees DECIMAL(18,2)
);
-- Insert data into the table variable
INSERT INTO @StudentDetailsTable (StudentID, StudentName, Course, Fees)
VALUES
(1, 'Peter', 'BSC', 60000.00),
(2, 'Leon', 'BA', 80000.00),
(3, 'Alex', 'Data Science', 50000.00);
-- Select data from the table variable
SELECT * FROM @StudentDetailsTable;
END;
- Scope: A temporary table established within a stored procedure is confined to that specific procedure and is automatically removed upon the completion of the procedure's execution.
- Multiple Sessions: Temporary tables that begin with a # symbol are restricted to the session in which they were created. For access across different sessions, a global temporary table can be created using ##; however, local tables are generally considered safer for use within stored procedures.
When to Utilize Temporary Tables?
- Intermediate Results: Temporary tables are advantageous for storing intermediate results of queries temporarily within a stored procedure.
- Data Transformation: They serve the purpose of manipulating or aggregating data prior to delivering the final result set.
- Performance: In certain scenarios, the use of temporary tables can enhance performance, particularly when managing complex joins or aggregations involving large datasets.
Process for utilizing a Table variable
A table variable in SQL Server is a distinct category of variable designed to hold a temporary collection of data organized in a table structure. It is defined through the DECLARE statement utilizing the TABLE data type. While table variables share similarities with temporary tables, they exhibit notable differences in terms of scope, performance, and application.
Limitations
- Prohibition of DDL Operations: DDL commands such as ALTER or DROP are not permitted on table variables.
- Restriction on Explicit Indexes: Only indexes that are established through constraints are permissible.
- Inefficiency with Large Data Sets: Table variables are not as effective as temporary tables when managing large volumes of data.
Step 1. To create a table variable, the appropriate syntax involves using the DECLARE statement in conjunction with the TABLE. Syntax for Declaring a Table Variable.
DECLARE @TableName TABLE
(
Column1 DataType PRIMARY KEY, -- Primary key example
Column2 DataType,
Column3 DataType
);
Step 2. Defining and Utilizing a Table Variable.
CREATE PROCEDURE StudentDetailsTableVariable
AS
BEGIN
DECLARE @StudentDetailsTable TABLE
(
StudentID INT PRIMARY KEY, -- Primary key example
StudentName NVARCHAR(100),
Course NVARCHAR(100),
Fees DECIMAL(18,2)
);
-- Insert data into the table variable
INSERT INTO @StudentDetailsTable (StudentID, StudentName, Course, Fees)
VALUES
(1, 'AmIt', 'BSC', 60000.00),
(2, 'Nagpal', 'BA', 80000.00),
(3, 'Prashant', 'Data Science', 50000.00);
-- Select data from the table variable
SELECT * FROM @StudentDetailsTable;
END;
When to Utilize Table Variables?
- Small Data Sets: Table variables are more effective for handling small amounts of data.
- Short Lifespan: These variables are automatically removed upon the completion of the batch or procedure.
- Stored Procedures: They function optimally within the context of a stored procedure or batch.
Key Distinctions Between Table Variables and Temporary Tables
HostForLIFEASP.NET SQL Server 2022 Hosting
