Temporary Tables and Table Variables

Difficulty: Intermediate
Generated on: 2025-07-10 02:29:03
Category: SQL Cheatsheet for Database Development

---

SQL Cheatsheet: Temporary Tables and Table Variables (Intermediate)

1. Quick Overview

Temporary Tables: Temporary tables are temporary storage structures that exist only for the duration of the current database session. They’re ideal for storing intermediate results, breaking down complex queries into smaller, manageable steps, and improving performance by reducing redundant calculations. They can be global (visible to all sessions) or local (visible only to the current session).

Table Variables: Table variables are similar to temporary tables but are declared like variables within a stored procedure, function, or batch. Their scope is limited to the batch where they are declared. They are stored in memory (or spill to disk if too large), and are generally faster for smaller datasets than temporary tables but have limitations on indexing and statistics.

When to Use:

• Temporary Tables:
  • Complex queries requiring multiple intermediate result sets.
  • Sharing data between multiple stored procedures or sessions (global temporary tables).
  • When you need to perform operations that are difficult or inefficient to do in a single query.
  • When you need to create indexes on the temporary data.
• Table Variables:
  • Smaller datasets where performance is critical within a stored procedure or function.
  • When you need a simple, scoped storage mechanism.
  • When you don’t need to create indexes or update statistics.

2. Syntax

Temporary Tables:

-- Local Temporary Table (visible only to the current session)
CREATE TABLE #MyTempTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Value INT
);

-- Global Temporary Table (visible to all sessions - use with caution!)
CREATE TABLE ##MyGlobalTempTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Value INT
);


-- Inserting Data
INSERT INTO #MyTempTable (ID, Name, Value)
VALUES (1, 'Item1', 10), (2, 'Item2', 20);

-- Selecting Data
SELECT * FROM #MyTempTable;

-- Dropping the table (optional, automatically dropped at the end of the session)
DROP TABLE #MyTempTable;

Table Variables:

-- Declare a table variable
DECLARE @MyTableVariable TABLE (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Value INT
);

-- Inserting Data
INSERT INTO @MyTableVariable (ID, Name, Value)
VALUES (1, 'Item1', 10), (2, 'Item2', 20);

-- Selecting Data
SELECT * FROM @MyTableVariable;

-- No need to drop, it's automatically deallocated when the scope ends.

3. Common Use Cases

• Intermediate Result Storage: Breaking down a complex query into smaller steps.

  -- Example: Calculate average order value for customers with more than 5 orders
  
  -- 1. Create a temporary table to store customer order counts
  CREATE TABLE #CustomerOrderCounts (
      CustomerID INT,
      OrderCount INT
  );
  
  -- 2. Populate the temporary table
  INSERT INTO #CustomerOrderCounts (CustomerID, OrderCount)
  SELECT CustomerID, COUNT(*)
  FROM Orders
  GROUP BY CustomerID
  HAVING COUNT(*) > 5;
  
  -- 3. Create a temporary table to store customer order totals
  CREATE TABLE #CustomerOrderTotals (
      CustomerID INT,
      TotalOrderValue DECIMAL(10,2)
  );
  
  INSERT INTO #CustomerOrderTotals(CustomerID, TotalOrderValue)
  SELECT CustomerID, SUM(OrderValue)
  FROM Orders
  GROUP BY CustomerID;
  
  -- 4. Calculate the average order value using the temporary tables
  SELECT AVG(tot.TotalOrderValue/coc.OrderCount) AS AverageOrderValue
  FROM #CustomerOrderCounts coc
  JOIN #CustomerOrderTotals tot ON coc.CustomerID = tot.CustomerID;
  
  -- Drop the temporary tables
  DROP TABLE #CustomerOrderCounts;
  DROP TABLE #CustomerOrderTotals;

• Passing Data to Stored Procedures: Using table variables to pass multiple rows to a stored procedure.

  -- Stored Procedure Definition
  CREATE PROCEDURE InsertProducts
      @Products AS ProductTableType READONLY -- User-Defined Table Type
  AS
  BEGIN
      INSERT INTO Products (ProductName, Price)
      SELECT ProductName, Price
      FROM @Products;
  END;
  
  -- User-Defined Table Type (SQL Server)
  CREATE TYPE ProductTableType AS TABLE (
      ProductName VARCHAR(50),
      Price DECIMAL(10, 2)
  );
  
  -- Usage Example
  DECLARE @MyProducts AS ProductTableType;
  
  INSERT INTO @MyProducts (ProductName, Price)
  VALUES ('Product A', 19.99), ('Product B', 29.99);
  
  EXEC InsertProducts @Products = @MyProducts;

• Data Transformation: Performing complex data transformations in stages.

  -- Example: Cleaning and transforming data before loading into a target table
  
  -- 1. Load data into a temporary table
  CREATE TABLE #RawData (
      RawCustomerID VARCHAR(50),
      RawOrderDate VARCHAR(50),
      RawAmount VARCHAR(50)
  );
  
  INSERT INTO #RawData (RawCustomerID, RawOrderDate, RawAmount)
  VALUES ('123', '2023-10-26', '100.00'), ('456', '2023-10-27', 'Invalid');
  
  -- 2. Create a temporary table with cleaned and transformed data
  CREATE TABLE #CleanedData (
      CustomerID INT,
      OrderDate DATE,
      Amount DECIMAL(10, 2)
  );
  
  -- 3. Populate the cleaned data table
  INSERT INTO #CleanedData (CustomerID, OrderDate, Amount)
  SELECT
      TRY_CAST(RawCustomerID AS INT),
      TRY_CAST(RawOrderDate AS DATE),
      TRY_CAST(RawAmount AS DECIMAL(10, 2))
  FROM #RawData
  WHERE TRY_CAST(RawCustomerID AS INT) IS NOT NULL
    AND TRY_CAST(RawOrderDate AS DATE) IS NOT NULL
    AND TRY_CAST(RawAmount AS DECIMAL(10, 2)) IS NOT NULL;
  
  -- 4. Insert the cleaned data into the target table
  INSERT INTO Orders (CustomerID, OrderDate, OrderValue)
  SELECT CustomerID, OrderDate, Amount
  FROM #CleanedData;
  
  -- 5. Drop temporary tables
  DROP TABLE #RawData;
  DROP TABLE #CleanedData;

4. Best Practices

• Keep it Short and Sweet: Use temporary tables and table variables only when necessary. Avoid overusing them.
• Scope Awareness: Use local temporary tables (#) unless you specifically need a global temporary table (##). Avoid global temporary tables if possible, due to concurrency and naming collision issues.
• Indexes (Temporary Tables Only): Create indexes on temporary tables if you’re performing complex queries on them. This can significantly improve performance. Remember, table variables do not support indexes (except primary key).
• Statistics (Temporary Tables Only): For very large temporary tables, consider updating statistics after data insertion, especially in SQL Server. This helps the query optimizer make better decisions. Table variables do not support statistics.
• Data Types: Use appropriate data types for columns in temporary tables and table variables to avoid data conversion issues.
• Cleanup: Explicitly drop temporary tables when you’re finished with them. While they’re automatically dropped at the end of the session, explicitly dropping them frees up resources sooner. Table variables are automatically cleaned up at the end of their scope.
• Performance Testing: Test the performance of queries that use temporary tables and table variables to ensure they’re actually improving performance. Sometimes, a well-optimized single query is faster.
• Avoid Large Datasets with Table Variables: Table variables are best suited for small datasets. For larger datasets, temporary tables are generally more efficient.
• Readonly Table Variables (SQL Server): When passing table variables to stored procedures, use the READONLY keyword to prevent the procedure from modifying the table variable. This can improve performance and prevent unintended data changes.

5. Examples

Example 1: Using a Temporary Table to Calculate Running Totals

-- Sample Data
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    OrderValue DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, OrderDate, OrderValue)
VALUES
(1, '2023-10-20', 100.00),
(2, '2023-10-21', 150.00),
(3, '2023-10-22', 200.00),
(4, '2023-10-23', 120.00);

-- Calculate running totals using a temporary table
CREATE TABLE #RunningTotals (
    OrderDate DATE,
    OrderValue DECIMAL(10, 2),
    RunningTotal DECIMAL(10, 2)
);

-- Populate the temporary table with running totals
INSERT INTO #RunningTotals (OrderDate, OrderValue, RunningTotal)
SELECT
    OrderDate,
    OrderValue,
    (SELECT SUM(OrderValue) FROM Orders AS o2 WHERE o2.OrderDate <= o1.OrderDate) AS RunningTotal
FROM Orders AS o1
ORDER BY OrderDate;

-- Display the results
SELECT * FROM #RunningTotals;

-- Drop the temporary table
DROP TABLE #RunningTotals;

-- Expected Output:
-- OrderDate    OrderValue   RunningTotal
-- 2023-10-20   100.00       100.00
-- 2023-10-21   150.00       250.00
-- 2023-10-22   200.00       450.00
-- 2023-10-23   120.00       570.00

Example 2: Using a Table Variable to Filter Data in a Stored Procedure

-- Sample Data
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price)
VALUES
(1, 'Product A', 19.99),
(2, 'Product B', 29.99),
(3, 'Product C', 39.99),
(4, 'Product D', 49.99);

-- Stored Procedure to filter products based on a list of IDs
CREATE PROCEDURE GetProductsByIds
    @ProductIds AS VARCHAR(MAX) -- Comma-separated list of ProductIDs
AS
BEGIN
    -- Declare a table variable to store the ProductIDs
    DECLARE @ProductIdTable TABLE (
        ProductID INT
    );

    -- Split the comma-separated string into individual ProductIDs and insert into the table variable
    INSERT INTO @ProductIdTable (ProductID)
    SELECT value
    FROM STRING_SPLIT(@ProductIds, ',');

    -- Select the products that match the ProductIDs in the table variable
    SELECT p.*
    FROM Products AS p
    INNER JOIN @ProductIdTable AS pt ON p.ProductID = pt.ProductID;
END;

-- Execute the stored procedure
EXEC GetProductsByIds @ProductIds = '1,3';

-- Expected Output:
-- ProductID   ProductName   Price
-- 1           Product A     19.99
-- 3           Product C     39.99

Example 3: Using a temporary table to generate a sequence of dates:

-- Example: Generating a sequence of dates for reporting purposes
DECLARE @StartDate DATE = '2023-10-01';
DECLARE @EndDate DATE = '2023-10-10';

-- Create a temporary table to store the date sequence
CREATE TABLE #DateSequence (
    DateValue DATE PRIMARY KEY
);

-- Populate the temporary table with dates
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #DateSequence (DateValue) VALUES (@StartDate);
    SET @StartDate = DATEADD(day, 1, @StartDate);
END;

-- Select the dates from the temporary table
SELECT DateValue FROM #DateSequence;

-- Drop the temporary table
DROP TABLE #DateSequence;

6. Common Pitfalls

• Naming Collisions (Global Temporary Tables): If multiple users create global temporary tables with the same name, conflicts can occur. Use unique names or avoid global temporary tables altogether.
• Scope Issues: Forgetting that temporary tables are session-specific or that table variables are scope-specific.
• Performance Degradation: Using temporary tables or table variables when a single, well-optimized query would be faster.
• Missing Indexes (Temporary Tables): Failing to create indexes on temporary tables, leading to slow query performance.
• Large Data Sets with Table Variables: Using table variables for large datasets, which can lead to memory pressure and performance issues.
• Statistics Issues (Temporary Tables in SQL Server): SQL Server’s query optimizer may not have accurate statistics for temporary tables, especially large ones, leading to suboptimal query plans. Consider UPDATE STATISTICS after populating a large temporary table.
• Incorrect Data Types: Using incorrect data types in temporary tables or table variables, leading to data conversion errors.
• Incorrect Syntax: Mistyping the syntax for creating or using temporary tables or table variables.
• Transaction Issues: Temporary tables created within a transaction are only visible within that transaction. If the transaction is rolled back, the temporary table is dropped.
• Security Considerations: Avoid storing sensitive data in global temporary tables as other users could potentially access them.

7. Database Variations

• MySQL:
  • Supports temporary tables.
  • Syntax is similar to SQL Server.
  • Uses CREATE TEMPORARY TABLE.
  • Does not support table variables directly. You can simulate them with stored procedures and local variables, but this is not as efficient.
• PostgreSQL:
  • Supports temporary tables.
  • Syntax is similar to SQL Server.
  • Uses CREATE TEMP TABLE or CREATE TEMPORARY TABLE.
  • Supports temporary tables within functions.
  • Does not have explicit table variables like SQL Server, but offers similar functionality using composite types and functions, albeit less straightforward.
• SQL Server:
  • Supports both temporary tables and table variables.
  • Provides the most comprehensive support for table variables, including user-defined table types.
  • UPDATE STATISTICS can be important for large temporary tables.
• Oracle:
  • Supports global temporary tables (with ON COMMIT PRESERVE ROWS) and private temporary tables (with ON COMMIT DELETE ROWS).
  • Does not have table variables directly. You can achieve similar results using PL/SQL collections (nested tables or VARRAYs), but this requires PL/SQL programming.

This cheatsheet provides a solid foundation for using temporary tables and table variables in SQL. Remember to adapt the techniques to your specific database system and application requirements. Always prioritize performance testing and code clarity.