Temporary Tables vs Table Variables in SQL Server Explained

A temporary table in SQL Server is a special type of table used to store data temporarily. It exists for the duration of a database session or batch and is automatically deleted when no longer needed. Temporary tables are stored in the tempdb system database.

Types of Temporary Tables

SQL Server supports two types of temporary tables.

1. Local Temporary Table (#)
   • Only visible to the session or connection that created it.
   • Automatically deleted when the session ends.
2. 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, 'Amit', 'MBA-IT', 160000.00),
        (2, 'Sagar', 'MBA-Economics', 180000.00),
        (3, 'Sanjay Kumar Jha', '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;

Step 3. Result of the above procedure.

Notes

1. 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.
2. 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?

1. Intermediate Results: Temporary tables are advantageous for storing intermediate results of queries temporarily within a stored procedure.
2. Data Transformation: They serve the purpose of manipulating or aggregating data prior to delivering the final result set.
3. 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

1. Prohibition of DDL Operations: DDL commands such as ALTER or DROP are not permitted on table variables.
2. Restriction on Explicit Indexes: Only indexes that are established through constraints are permissible.
3. 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;

Step 3. Result of the above implementation.

When to Utilize Table Variables?

1. Small Data Sets: Table variables are more effective for handling small amounts of data.
2. Short Lifespan: These variables are automatically removed upon the completion of the batch or procedure.
3. Stored Procedures: They function optimally within the context of a stored procedure or batch.

Key Distinctions Between Table Variables and Temporary Tables