SQL Views, Temp Tables, Table Variables, and CTEs: When to Use Which and Why

Every complex query needs intermediate results. You query 5 tables, join them, filter, aggregate, and then join again with another result. Where do you put the intermediate data? SQL gives you four options: Views, Temp Tables, Table Variables, and CTEs. Each serves a different purpose.

Think of it like cooking. A CTE is prep work done on the counter — immediate, one-use, cleaned up after the dish is served. A temp table is a storage container in the fridge — persists for the session, reusable, but gone when you leave. A table variable is a small bowl — quick, in-memory, for small amounts. A view is a recipe card — does not contain food, just instructions to make it on demand.

Table of Contents

• Views — Saved Queries (Virtual Tables)
• When to Use Views
• Temp Tables — Session-Level Storage
• Local vs Global Temp Tables
• When to Use Temp Tables
• Table Variables — In-Memory Small Storage
• When to Use Table Variables
• Temp Tables vs Table Variables
• Views vs Temp Tables vs Table Variables vs CTEs
• Materialized Views / Indexed Views
• Common Patterns in Production
• Common Mistakes
• Interview Questions
• Wrapping Up

Views — Saved Queries (Virtual Tables)

A view is a saved SELECT statement that acts like a virtual table. It stores no data — every time you query the view, it runs the underlying SELECT.

-- Create a view
CREATE VIEW vw_active_employees AS
SELECT e.emp_id, e.first_name, e.last_name, e.salary,
       d.dept_name, e.hire_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.is_active = 1;

-- Use it like a table
SELECT * FROM vw_active_employees WHERE dept_name = 'Engineering';
SELECT dept_name, AVG(salary) FROM vw_active_employees GROUP BY dept_name;

-- Drop a view
DROP VIEW IF EXISTS vw_active_employees;

Views Do NOT Store Data

-- When you query the view:
SELECT * FROM vw_active_employees WHERE salary > 90000;

-- The database actually executes:
SELECT * FROM (
    SELECT e.emp_id, e.first_name, e.last_name, e.salary, d.dept_name, e.hire_date
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    WHERE e.is_active = 1
) AS vw
WHERE salary > 90000;

Updatable Views

Simple views (single table, no aggregation) can be used for INSERT, UPDATE, DELETE:

CREATE VIEW vw_engineering AS
SELECT emp_id, first_name, salary FROM employees WHERE department = 'Engineering';

-- Update through the view (updates the actual table!)
UPDATE vw_engineering SET salary = 110000 WHERE emp_id = 1001;

Views with SCHEMABINDING

-- Prevents underlying table changes that would break the view
CREATE VIEW vw_emp_summary WITH SCHEMABINDING AS
SELECT department, COUNT_BIG(*) AS emp_count, SUM(salary) AS total_salary
FROM dbo.employees
GROUP BY department;

When to Use Views

• Simplify complex queries — hide multi-table joins behind a simple name
• Security — give users access to the view (filtered columns) without access to base tables
• Consistency — ensure everyone uses the same business logic (active employees filter)
• Backward compatibility — change the underlying table but keep the view name the same

Temp Tables — Session-Level Storage

Temp tables are real tables stored in tempdb. They persist for the duration of your session (or stored procedure execution) and are automatically dropped when the session ends.

-- Create temp table (# prefix makes it temporary)
CREATE TABLE #active_employees (
    emp_id INT,
    full_name VARCHAR(100),
    salary DECIMAL(10,2),
    department VARCHAR(50)
);

-- Insert data
INSERT INTO #active_employees
SELECT emp_id, CONCAT(first_name, ' ', last_name), salary, department
FROM employees
WHERE is_active = 1;

-- Use it multiple times in the session
SELECT * FROM #active_employees WHERE department = 'Engineering';
SELECT department, AVG(salary) FROM #active_employees GROUP BY department;
SELECT COUNT(*) FROM #active_employees;

-- Explicitly drop (optional — auto-drops when session ends)
DROP TABLE IF EXISTS #active_employees;

Create Temp Table from Query (SELECT INTO)

-- Fastest way — creates table and fills it in one step
SELECT emp_id, first_name, salary, department
INTO #high_earners
FROM employees
WHERE salary > 90000;

SELECT * FROM #high_earners;

Add Indexes to Temp Tables

CREATE TABLE #large_dataset (
    id INT,
    category VARCHAR(50),
    amount DECIMAL(10,2)
);

-- Add index for performance
CREATE INDEX IX_temp_category ON #large_dataset (category);

Local vs Global Temp Tables

-- Local temp table (# prefix): visible only in current session
CREATE TABLE #my_temp (id INT);
-- Session 1 can see it. Session 2 CANNOT.

-- Global temp table (## prefix): visible to ALL sessions
CREATE TABLE ##shared_temp (id INT);
-- Session 1 AND Session 2 can see it.
-- Dropped when the LAST session referencing it disconnects.

When to Use Temp Tables

• Intermediate results used MULTIPLE times in complex logic
• Performance — compute expensive joins/aggregations once, reuse the result
• Large intermediate datasets (thousands to millions of rows)
• Adding indexes for performance on intermediate data
• Stored procedure logic — build up results step by step

Table Variables — In-Memory Small Storage

Table variables are declared with DECLARE and behave like variables, not tables:

-- Declare a table variable
DECLARE @high_earners TABLE (
    emp_id INT,
    full_name VARCHAR(100),
    salary DECIMAL(10,2)
);

-- Insert data
INSERT INTO @high_earners
SELECT emp_id, CONCAT(first_name, ' ', last_name), salary
FROM employees WHERE salary > 90000;

-- Use it
SELECT * FROM @high_earners;

-- No need to DROP — automatically gone when batch ends

When to Use Table Variables

• Small datasets (under 1,000 rows) for quick lookups
• Single batch operations (cannot span multiple batches)
• Functions — table variables can be used inside functions (temp tables cannot)
• When you want no transaction log overhead (table variables have minimal logging)

Temp Tables vs Table Variables

Feature	Temp Table (#)	Table Variable (@)
Storage	tempdb (disk)	Memory (initially), spills to tempdb
Scope	Session / stored procedure	Current batch only
Indexes	✅ Yes (CREATE INDEX)	❌ No (only PK/UNIQUE inline)
Statistics	✅ Yes (optimizer knows row count)	❌ No (optimizer assumes 1 row!)
Best for	Large datasets (1K+ rows)	Small datasets (<1K rows)
Transaction	Participates in transactions	Does NOT roll back with transactions
DROP needed	Optional (auto-drops on session end)	No (auto-drops on batch end)
Performance on large data	✅ Fast (has statistics + indexes)	❌ Slow (no statistics, bad plans)

The rule: Use temp tables for anything over 100-1,000 rows. Use table variables for small lookup lists or inside functions.

Views vs Temp Tables vs Table Variables vs CTEs

Feature	View	Temp Table	Table Variable	CTE
Stores data?	No (virtual)	Yes (tempdb)	Yes (memory)	No (inline)
Persists?	Permanent (until dropped)	Session	Batch	Single query only
Reusable?	✅ Across queries	✅ Within session	✅ Within batch	❌ Single query
Indexed?	Underlying table indexes	✅ Yes	❌ No	❌ No
Best for	Simplifying access	Large intermediate results	Small lookup lists	Recursive queries, readability
Performance	Same as underlying query	Good (indexed)	Good (small data)	Depends on query

Decision Tree:

Need to reuse across sessions/users?
  → VIEW

Need to reuse within the same session with large data?
  → TEMP TABLE (#)

Need a quick small lookup in a single batch?
  → TABLE VARIABLE (@)

Need it for just one query (readability or recursion)?
  → CTE (WITH ... AS)

Materialized Views / Indexed Views

Unlike regular views, an indexed view (SQL Server) or materialized view (PostgreSQL) physically stores the computed result:

-- SQL Server: Indexed view (requires SCHEMABINDING)
CREATE VIEW vw_dept_summary WITH SCHEMABINDING AS
SELECT department, COUNT_BIG(*) AS emp_count, SUM(salary) AS total_salary
FROM dbo.employees
GROUP BY department;

-- Create unique clustered index on the view (materializes it)
CREATE UNIQUE CLUSTERED INDEX IX_dept_summary
ON vw_dept_summary (department);

-- Now the aggregation is PRE-COMPUTED and stored
-- Queries against this view read the stored result — instant
SELECT * FROM vw_dept_summary;

Trade-off: Faster reads (pre-computed) but slower writes (index must be updated on every INSERT/UPDATE/DELETE to the base table).

Common Patterns in Production

Pattern 1: Staging with Temp Table

-- Step 1: Load raw data into temp table
SELECT * INTO #staging FROM external_source.raw_data;

-- Step 2: Clean and validate
DELETE FROM #staging WHERE customer_id IS NULL;
UPDATE #staging SET email = LOWER(TRIM(email));

-- Step 3: Merge into production
MERGE INTO customers AS target
USING #staging AS source ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN INSERT VALUES (source.customer_id, source.email, source.name);

DROP TABLE #staging;

Pattern 2: Security Layer with Views

-- Base table: contains all data including PII
CREATE TABLE customers (id INT, name VARCHAR(100), ssn VARCHAR(11), salary DECIMAL);

-- View for analysts: hides sensitive columns
CREATE VIEW vw_customers_safe AS
SELECT id, name FROM customers;
-- Analysts get SELECT on the view, NOT on the base table

-- View for HR: shows salary but masks SSN
CREATE VIEW vw_customers_hr AS
SELECT id, name, '***-**-' + RIGHT(ssn, 4) AS masked_ssn, salary FROM customers;

Pattern 3: CTE for Readability

-- Complex query broken into readable CTEs
WITH active_emps AS (
    SELECT * FROM employees WHERE is_active = 1
),
dept_stats AS (
    SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS headcount
    FROM active_emps GROUP BY department
),
above_avg AS (
    SELECT e.*, d.avg_salary
    FROM active_emps e
    JOIN dept_stats d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT * FROM above_avg ORDER BY salary DESC;

Common Mistakes

1. Using table variables for large datasets — the optimizer assumes 1 row, creating terrible execution plans. Use temp tables for anything over 1,000 rows.

2. Not indexing temp tables — a temp table with 1 million rows and no index is as slow as a real table without an index. Add indexes on columns you filter or join on.

3. Creating views that are too complex — a view with 10 joins, 5 subqueries, and 3 CTEs is impossible to debug and optimize. Keep views simple and composable.

4. Forgetting to drop temp tables — while auto-dropped on session end, long-running sessions can accumulate temp tables and consume tempdb space. Drop explicitly when done.

5. Using a view when a temp table would be faster — if you reference a complex view 5 times in one query, the underlying SELECT runs 5 times. A temp table computes once and is reused.

Interview Questions

Q: What is the difference between a view and a temp table? A: A view is a saved SELECT statement that stores no data — it executes the query every time. A temp table physically stores data in tempdb for the duration of the session. Use views for simplifying access and security. Use temp tables for storing intermediate results that are reused multiple times.

Q: When should you use a temp table vs a table variable? A: Temp tables for large datasets (1,000+ rows) — they support indexes and statistics for optimal query plans. Table variables for small datasets (under 1,000 rows) — less overhead but no statistics, so the optimizer assumes 1 row. Use table variables inside functions where temp tables are not allowed.

Q: What is an indexed view? A: A view with a unique clustered index that physically stores the computed result. Regular views re-execute the query every time. Indexed views store the result and update it automatically when base tables change. Faster reads but slower writes.

Wrapping Up

Four tools, four purposes: Views simplify access. Temp tables store large intermediate results. Table variables handle small lookups. CTEs improve readability. Pick based on data size, reuse needs, and persistence requirements.

Related posts: – CTEs & Subqueries – SQL Indexes & Execution Plans – SQL DDL, DML, Constraints

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.