TECH **SQL for Data Analysis: Temporary Tables & Table Variables – Zero-Fluff Study Guide**

SQL for Data Analysis: Temporary Tables & Table Variables – Zero-Fluff Study Guide

1. What This Is & Why It Matters

Temporary tables and table variables are SQL constructs that let you store intermediate results in memory or disk without cluttering your production database. Think of them like sticky notes for your query: you jot down a calculation, use it a few times, then toss it when you’re done.

Why This Matters in Production

• Performance: Avoid recalculating the same subquery repeatedly (e.g., filtering a large dataset once and reusing it).
• Readability: Break complex queries into logical steps (e.g., "First clean the data, then aggregate").
• Isolation: Run ad-hoc analyses without risking permanent changes to production tables.
• Debugging: Test transformations step-by-step before finalizing a report or ETL pipeline.

Real-World Scenario:
You’re analyzing customer churn for a SaaS company. Your raw data is in a user_activity table with 50M rows. You need to: 1. Filter active users from the last 90 days.
2. Join with a subscription table to flag canceled accounts.
3. Calculate churn rates by cohort.

Without temporary tables, you’d either: - Write a monstrous nested query (hard to debug), or - Create a permanent table (clutters the database, requires cleanup).

With temporary tables, you: 1. Store the filtered users in #active_users.
2. Join with subscriptions in #churn_candidates.
3. Aggregate in a final query—all without touching production tables.

2. Core Concepts & Components

1. Temporary Table (#table_name)

• Definition: A table that exists only for the duration of your session (or stored procedure). Stored in tempdb (SQL Server) or a temporary schema (PostgreSQL).
• Production Insight: Use for large intermediate datasets (10K+ rows) or when you need indexes for performance.
• Syntax:
  sql CREATE TABLE #temp_users (
user_id INT,
signup_date DATE,
is_active BIT );

2. Global Temporary Table (##table_name)

• Definition: A temporary table visible to all sessions (until the creating session ends). Rarely used—risk of race conditions.
• Production Insight: Avoid unless you’re debugging across sessions (e.g., sharing data between SSMS tabs).

3. Table Variable (@table_name)

• Definition: A variable that holds a table-like structure in memory. Scoped to the batch, stored procedure, or function.
• Production Insight: Best for small datasets (<1K rows) or when you need deterministic behavior (e.g., in functions).
• Syntax:
  sql DECLARE @user_ids TABLE (id INT PRIMARY KEY);

4. Common Table Expression (CTE) (WITH clause)

• Definition: A named temporary result set defined within a query. Not stored—executes every time it’s referenced.
• Production Insight: Use for readability (e.g., breaking down complex joins) but not for repeated use (re-executes each time).
• Syntax:
  sql WITH active_users AS (
SELECT user_id FROM users WHERE last_login > DATEADD(day, -90, GETDATE()) ) SELECT * FROM active_users;

5. Tempdb (SQL Server)

• Definition: System database where temporary tables and table variables are stored.
• Production Insight: Heavy temp table usage can bloat tempdb—monitor disk space and performance.

6. Scope & Lifetime

7. Indexes on Temporary Tables

• Definition: You can add indexes to #temp_tables (but not @table_variables in most SQL dialects).
• Production Insight: Indexes speed up joins/filters but add overhead—use only if the table is reused multiple times.
• Syntax:
  sql CREATE CLUSTERED INDEX IX_temp_users_id ON #temp_users(user_id);

8. Statistics & Query Optimization

• Definition: SQL Server automatically creates statistics for #temp_tables but not for @table_variables (unless using OPTION (RECOMPILE)).
• Production Insight: For large @table_variables, use OPTION (RECOMPILE) to force the optimizer to consider actual row counts.

3. Step-by-Step Hands-On: Analyzing Churn with Temporary Tables

Prerequisites

• SQL Server (or PostgreSQL with adjustments—see notes).
• A database with users and subscriptions tables (sample schema below).
• Basic familiarity with SELECT, JOIN, and GROUP BY.

Sample Schema

-- Users table
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    signup_date DATE,
    email VARCHAR(100)
);

-- Subscriptions table
CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    user_id INT,
    plan_name VARCHAR(50),
    start_date DATE,
    end_date DATE,  -- NULL if active
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Task: Calculate Churn Rate by Cohort

1. Filter active users (logged in last 90 days).
2. Identify churned users (subscriptions ended in the last 30 days).
3. Calculate churn rate (churned users / active users) by signup cohort.

Step 1: Create a Temporary Table for Active Users

-- Step 1: Filter active users (logged in last 90 days)
CREATE TABLE #active_users (
    user_id INT PRIMARY KEY,
    signup_date DATE,
    last_login DATE
);

INSERT INTO #active_users
SELECT
    u.user_id,
    u.signup_date,
    MAX(s.start_date) AS last_login  -- Approximate last activity
FROM users u
JOIN subscriptions s ON u.user_id = s.user_id
WHERE s.start_date > DATEADD(day, -90, GETDATE())
GROUP BY u.user_id, u.signup_date;

Verification:

SELECT COUNT(*) FROM #active_users;  -- Should return >0 if data exists

Step 2: Create a Temporary Table for Churned Users

-- Step 2: Identify churned users (subscriptions ended in last 30 days)
CREATE TABLE #churned_users (
    user_id INT PRIMARY KEY,
    churn_date DATE
);

INSERT INTO #churned_users
SELECT
    s.user_id,
    s.end_date
FROM subscriptions s
WHERE s.end_date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE();

Verification:

SELECT * FROM #churned_users;  -- Check a few rows

Step 3: Join and Aggregate for Churn Rate

-- Step 3: Calculate churn rate by cohort
SELECT
    DATEPART(year, au.signup_date) AS signup_year,
    DATEPART(month, au.signup_date) AS signup_month,
    COUNT(DISTINCT au.user_id) AS active_users,
    COUNT(DISTINCT cu.user_id) AS churned_users,
    CAST(COUNT(DISTINCT cu.user_id) AS FLOAT) / COUNT(DISTINCT au.user_id) AS churn_rate
FROM #active_users au
LEFT JOIN #churned_users cu ON au.user_id = cu.user_id
GROUP BY DATEPART(year, au.signup_date), DATEPART(month, au.signup_date)
ORDER BY signup_year, signup_month;

Expected Output:
| signup_year | signup_month | active_users | churned_users | churn_rate | |-------------|--------------|--------------|---------------|------------| | 2023 | 1 | 1000 | 50 | 0.05 | | 2023 | 2 | 1200 | 60 | 0.05 |

Step 4: Clean Up (Optional)

-- Drop temporary tables (automatically dropped when session ends)
DROP TABLE #active_users;
DROP TABLE #churned_users;

4. ? Production-Ready Best Practices

Performance

• Use #temp_tables for large datasets (>1K rows) or when you need indexes.
• Use @table_variables for small datasets (<1K rows) or in functions.
• Add indexes to #temp_tables if they’re reused (e.g., in loops or multiple joins): sql CREATE CLUSTERED INDEX IX_temp_user_id ON #temp_users(user_id);
• Avoid SELECT *—explicitly list columns to reduce memory usage.

Readability & Maintainability

• Prefix temporary tables with # (e.g., #active_users) for clarity.
• Add comments explaining the purpose of each temp table: sql -- #active_users: Users with activity in the last 90 days CREATE TABLE #active_users (...);
• Use descriptive names (e.g., #churned_users_2023 instead of #temp1).

Debugging

• Check tempdb usage if queries slow down: sql -- SQL Server: Monitor tempdb space SELECT
SUM(user_object_reserved_page_count) * 8 / 1024 AS temp_tables_mb,
SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb FROM sys.dm_db_file_space_usage WHERE database_id = DB_ID('tempdb');
• Log temp table creation in stored procedures for debugging: sql PRINT 'Creating #active_users...'; CREATE TABLE #active_users (...);

Security

• Temporary tables are session-scoped—other users can’t access them (unless using ##global_temp).
• Avoid storing sensitive data in temp tables (e.g., PII)—they’re still logged in tempdb.

Alternatives

• CTEs (WITH clause): Use for single-query readability (but re-executes each time).
• Derived tables: Use for one-off subqueries (e.g., FROM (SELECT ...) AS subquery).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. When to use #temp_table vs @table_variable:

2. "You need to store 50K rows for a complex report. Which should you use?"
   Answer: #temp_table (better for large datasets and indexing).

3. Scope of temporary objects:

4. "A stored procedure creates a #temp_table. Can another procedure access it?"
   Answer: No—#temp_tables are session-scoped.

5. Performance implications:

6. "Why might a query using @table_variable run slower than one using #temp_table?"
   Answer: @table_variables lack statistics, leading to poor query plans.

7. CTE vs Temporary Table:

8. "You need to reuse a subquery 5 times in a single query. Should you use a CTE or #temp_table?"
   Answer: #temp_table (CTEs re-execute each time).

Key Trap Distinctions

Scenario-Based Question

"You’re writing a stored procedure that processes 10K rows. The procedure runs slowly, and the query plan shows a table scan on a @table_variable. What’s the fix?" Answer:
1. Replace @table_variable with #temp_table.
2. Add an index to the #temp_table.
3. Alternatively, use OPTION (RECOMPILE) with the @table_variable to force statistics.

7. ? Hands-On Challenge

Challenge:
You have a sales table with columns: order_id, customer_id, order_date, amount. Write a query to: 1. Find customers who placed orders in both 2022 and 2023.
2. Calculate their total spending in 2023.

Constraints:
- Use a temporary table to store customers who ordered in 2022.
- Use a table variable to store 2023 orders for those customers.

Solution:

-- Step 1: Store 2022 customers in a temp table
CREATE TABLE #customers_2022 (
    customer_id INT PRIMARY KEY
);

INSERT INTO #customers_2022
SELECT DISTINCT customer_id
FROM sales
WHERE YEAR(order_date) = 2022;

-- Step 2: Use a table variable for 2023 orders (small dataset)
DECLARE @customers_2023 TABLE (
    customer_id INT,
    total_spend DECIMAL(10,2)
);

INSERT INTO @customers_2023
SELECT
    customer_id,
    SUM(amount) AS total_spend
FROM sales
WHERE customer_id IN (SELECT customer_id FROM #customers_2022)
  AND YEAR(order_date) = 2023
GROUP BY customer_id;

-- Step 3: Return results
SELECT * FROM @customers_2023 ORDER BY total_spend DESC;

Why It Works:
- #customers_2022 stores the filtered 2022 customers (large dataset → temp table).
- @customers_2023 aggregates 2023 spending (small dataset → table variable).
- The IN clause efficiently filters 2023 orders to only customers from 2022.

8. ? Rapid-Reference Crib Sheet

Temporary Tables (#table_name)

-- Create
CREATE TABLE #temp_table (id INT PRIMARY KEY, name VARCHAR(50));

-- Insert
INSERT INTO #temp_table SELECT id, name FROM source_table;

-- Index
CREATE CLUSTERED INDEX IX_temp_id ON #temp_table(id);

-- Drop (optional)
DROP TABLE #temp_table;

Table Variables (@table_name)

-- Declare
DECLARE @table_var TABLE (id INT PRIMARY KEY, value DECIMAL(10,2));

-- Insert
INSERT INTO @table_var SELECT id, amount FROM source_table;

-- Use in query
SELECT * FROM @table_var WHERE id = 1;

CTEs (WITH clause)

WITH cte_name AS (
    SELECT id, name FROM source_table WHERE condition
)
SELECT * FROM cte_name;

Key Differences

⚠️ Exam Traps

• @table_variable has no statistics → Use OPTION (RECOMPILE) for large datasets.
• #temp_table is session-scoped → Not visible to other sessions (unless ##global_temp).
• CTEs re-execute → Not stored; avoid for repeated use.
• tempdb bloat → Monitor disk space if using many temp tables.

9. ? Where to Go Next

1. Microsoft Docs: Temporary Tables
2. Official guide to #temp_tables and ##global_temp.
3. SQLShack: Table Variables vs Temp Tables
4. Deep dive into performance differences.
5. PostgreSQL Docs: Temporary Tables
6. PostgreSQL-specific syntax and behavior.
7. Book: "T-SQL Querying" by Itzik Ben-Gan (Chapter 10: Temporary Tables and Table Variables)
8. Advanced patterns and optimizations.