SQL-CTE vs Temp Table vs Table Variable

Temporary Table, Table Variable, and Common Table Expression (CTE) These are techniques in SQL to store intermediate results for further processing. Each has distinct characteristics, advantages, and limitations.

Before starting each we explain simple word -  Session and Batch .

Session

A session represents the connection between a client application and the SQL Server. It begins when the client connects to the server and ends when the client disconnects

• Scope - A session persists as long as the connection remains active.
• Session ID - Each session is uniquely identified by a Session ID (SPID in SQL Server)

Example

• If a client application connects to a SQL Server, executes multiple commands, and maintains an open connection, all commands run within the same session.

Batch

• Definition - A batch is a group of one or more SQL statements that are sent to the SQL Server for execution as a single unit.
• Scope - A batch is executed within the context of a session, and its scope is limited to its execution.
• Execution - When SQL Server processes a batch, it compiles the statements into a single execution plan (where possible). Each batch has a local scope for variables declared within it.
• Use Case - Batches are used to logically group SQL statements that should execute together.

Example

-- Batch 1
DECLARE @Variable1 INT = 10;
SELECT @Variable1;

GO -- Marks the end of Batch 1

-- Batch 2
-- This batch cannot access @Variable1 from Batch 1
SELECT @Variable1; -- This will result in an error

Real-World Analogy

• Session - Like an open phone call between two people - communication continues until the call is disconnected.
• Batch - Like a single message or a group of messages sent during the call - they are processed and replied to but don't affect other messages directly.

1. Temporary Table

A temporary table is a physical table stored in the tempdb database. It is useful for storing intermediate results and can be indexed.

• Scope - Exists for the duration of the session or batch.
  • Local (#TempTable) - Accessible only within the session or batch where it is created.
  • Global (##TempTable) - Accessible across all sessions but deleted when the creating session ends.
• Use Case - Use when dealing with large datasets requiring indexing, constraints, or multiple references in a long process.

Syntax

CREATE TABLE #TempTable (EmployeeID INT, Salary INT);
INSERT INTO #TempTable VALUES (1, 50000), (2, 60000);

SELECT * FROM #TempTable;

Real-World Example

Suppose you are generating a sales report and need to process large transactional data.

CREATE TABLE #SalesSummary (Region VARCHAR(50), TotalSales DECIMAL(10, 2));
INSERT INTO #SalesSummary
SELECT Region, SUM(SalesAmount)
FROM Sales
GROUP BY Region;

SELECT * FROM #SalesSummary
WHERE TotalSales > 100000;

2. Table Variable

A table variable is an in-memory data structure. It is declared using the DECLARE statement and behaves like a variable.

• Scope - Limited to the batch, stored procedure, or function where it is declared.
• Use Case - Use for small datasets, when you don't need indexing or constraints.

Syntax

DECLARE @TableVariable TABLE (EmployeeID INT, Salary INT);
INSERT INTO @TableVariable VALUES (1, 50000), (2, 60000);

SELECT * FROM @TableVariable;

Real-World Example

For a stored procedure that needs a temporary dataset for simple calculations.

DECLARE @SalesSummary TABLE (Region VARCHAR(50), TotalSales DECIMAL(10, 2));

INSERT INTO @SalesSummary
SELECT Region, SUM(SalesAmount)
FROM Sales
GROUP BY Region;

SELECT * FROM @SalesSummary WHERE TotalSales > 100000;

3. Common Table Expression (CTE)

A CTE is a temporary, named result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

• Scope - Exists only during the execution of the statement in which it is defined.
• Use Case - Use for recursive queries, simplifying complex queries, or when you don’t need indexing or reuse.

Syntax

WITH CTE_SalesSummary AS (
    SELECT Region, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT * FROM CTE_SalesSummary WHERE TotalSales > 100000;

Real-World Example

Finding hierarchical data, such as an employee reporting structure.

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Difference between the Temporary Table vs Table Variable vs CTE