SQL : Temp Tables, Table-Valued Parameters, Global Temp Tables, and CTEs

Structured Query Language (SQL) is a powerful tool for managing and querying databases.

In this blog post, we'll delve into some SQL Server features that are often used in different scenarios: Temp Tables, Table-Valued Parameters, Global Temp Tables, and Common Table Expressions (CTEs).

We'll explore each concept, provide SQL Server code snippets, and discuss when to use each.

Temp Tables

Temp Tables are temporary storage structures that exist only for the duration of a session or a batch.

They are used to store and process intermediate results during complex queries.

-- Create a Temp Table
CREATE TABLE #TempTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);
 
-- Insert data into Temp Table
INSERT INTO #TempTable (ID, Name)
VALUES (1, 'John'), (2, 'Jane');
 
-- Query Temp Table
SELECT * FROM #TempTable;
 
-- Drop Temp Table at the end of the session or batch
DROP TABLE #TempTable;

Table-Valued Parameters (TVPs)

Table-Valued Parameters allow you to pass a table structure as a parameter to a stored procedure or a function.

They are useful when you need to pass multiple rows of data to a stored procedure or function.

-- Create a User-Defined Table Type
CREATE TYPE dbo.EmployeeType AS TABLE (
    ID INT,
    Name NVARCHAR(50)
);
 
-- Create a stored procedure using TVP
CREATE PROCEDURE usp_InsertEmployees
    @Employees dbo.EmployeeType READONLY
AS
BEGIN
    INSERT INTO Employees (ID, Name)
    SELECT ID, Name FROM @Employees;
END;
 
-- Declare a variable of TVP type
DECLARE @EmployeeData dbo.EmployeeType;
 
-- Insert data into TVP variable
INSERT INTO @EmployeeData (ID, Name)
VALUES (1, 'Alice'), (2, 'Bob');
 
-- Execute stored procedure with TVP
EXEC usp_InsertEmployees @Employees = @EmployeeData;

Global Temp Tables

Global Temp Tables are similar to Temp Tables, but their scope extends beyond a single session.

They persist until all sessions referencing them complete.

-- Create a Global Temp Table
CREATE TABLE ##GlobalTempTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);
 
-- Insert data into Global Temp Table
INSERT INTO ##GlobalTempTable (ID, Name)
VALUES (1, 'Mike'), (2, 'Sara');
 
-- Query Global Temp Table
SELECT * FROM ##GlobalTempTable;
 
-- Drop Global Temp Table explicitly when no longer needed
DROP TABLE ##GlobalTempTable;

Common Table Expressions (CTEs)

CTEs are named result sets that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.

They are useful for simplifying complex queries and enhancing code readability.

-- Create a Common Table Expression (CTE)
WITH DepartmentCTE AS (
    SELECT
        ID,
        Name,
        ManagerID
    FROM
        Departments
    WHERE
        Location = 'Headquarters'
)
-- Use the CTE in a SELECT statement
SELECT
    E.EmployeeID,
    E.EmployeeName,
    DCTE.Name AS DepartmentName
FROM
    Employees E
JOIN
    DepartmentCTE DCTE ON E.DepartmentID = DCTE.ID;

Differentiating and Use Cases

• Temp Tables vs. Table-Valued Parameters
• Use Temp Tables for temporary storage and processing during a session or batch.
• Use Table-Valued Parameters when you need to pass structured data to stored procedures or functions.
• Global Temp Tables
• Use Global Temp Tables when you need temporary storage that persists across multiple sessions.
• CTEs
• Use CTEs for simplifying complex queries, improving readability, and avoiding the need for creating permanent views.

Conclusion

Understanding the use cases and syntax of Temp Tables, Table-Valued Parameters, Global Temp Tables, and CTEs is crucial for efficient database management. 

Whether you need temporary storage, structured parameter passing, persistent global storage, or query simplification, SQL Server provides a range of tools to meet your needs. Choosing the right tool for the job can greatly enhance the performance, scalability, and maintainability of your SQL Server solutions.

Happy querying!