Lesson 7: Temp Tables vs Table Variables (Practical DBA Usage)

Section 3 — Lesson 7: Temp Tables vs Table Variables (Practical DBA Usage)

Welcome back. In the last lesson you learned safe write patterns for INSERT/UPDATE/DELETE.
Now we move to a tool DBAs use constantly during troubleshooting, reporting, and operational scripting:
temporary storage.

When you diagnose incidents or build repeatable scripts, you often need to:

• capture intermediate results from DMVs
• store a list of target rows (IDs) before making changes
• aggregate data in phases (stage → clean → summarize)
• compare “before vs after” values
• produce a final report that is easy to read

SQL Server gives you two primary tools for this:
temporary tables and table variables.
They look similar, but they behave differently—especially for performance.

Lesson description: You will learn what temp tables (#temp) and table variables (@table) are,
how they differ in scope, indexing, statistics, and performance, when DBAs choose each one,
safe patterns for incident scripts, and how to avoid common mistakes that lead to slow queries or incorrect results.

1) Quick Definitions

Temp table (#TempTable)

• Created in tempdb (just like other temp objects).
• Can be indexed (including creating indexes after insert).
• Can have statistics (SQL Server can use better estimates).
• Works well for larger row counts and complex joins/aggregations.
• Visible to the session (connection) that created it; can also be global (##) but DBAs rarely use global temps.

Table variable (@TableVar)

• Declared like a variable (lives in memory conceptually, but still uses tempdb storage under the hood in many cases).
• Scope is the batch/procedure where it’s declared.
• Historically had limited statistics behavior (optimizer often assumes very small row counts), which can hurt performance.
• Best for smaller datasets and simple logic.

DBA rule: If you are unsure and expect non-trivial row counts, start with a temp table.
It is usually the safer default for real-world DBA scripts.

2) Scope and Lifetime (How Long They Exist)

2A) Temp table scope

A local temp table (#Something) exists for your current session until you drop it or disconnect.
It is visible to that session, and to nested procedure calls within that session.

-- Create a temp table
CREATE TABLE #TargetBookings
(
    BookingID INT NOT NULL PRIMARY KEY,
    Status    VARCHAR(20) NOT NULL
);

-- Use it
INSERT INTO #TargetBookings (BookingID, Status)
SELECT BookingID, Status
FROM dbo.Booking
WHERE Status IN ('NEW','PENDING');

-- It remains available in this session until dropped or session ends
SELECT * FROM #TargetBookings;

DROP TABLE #TargetBookings;

2B) Table variable scope

A table variable exists only within the batch/procedure where it is declared.

DECLARE @TargetBookings TABLE
(
    BookingID INT NOT NULL PRIMARY KEY,
    Status    VARCHAR(20) NOT NULL
);

INSERT INTO @TargetBookings (BookingID, Status)
SELECT BookingID, Status
FROM dbo.Booking
WHERE Status IN ('NEW','PENDING');

SELECT * FROM @TargetBookings;

Once the batch ends, @TargetBookings is gone.

3) The DBA Difference That Matters Most: Performance

DBAs care about performance because slow scripts can:

• cause blocking
• inflate tempdb usage
• increase log usage
• create CPU spikes
• turn a small problem into an outage

Here is the key performance difference:

• Temp tables generally allow SQL Server to create/use statistics and produce better execution plans.
• Table variables often lead the optimizer to assume a small number of rows, which can cause bad plan choices (like nested loops on huge data).

This is why temp tables are commonly the “DBA default” for troubleshooting scripts that handle more than a tiny set of rows.

4) Indexing and Constraints (What You Can Do With Each)

4A) Temp tables: flexible indexing

You can create indexes after loading data (useful when you do not know how many rows will be staged).

CREATE TABLE #PaymentsStage
(
    PaymentID     INT NOT NULL,
    BookingID     INT NOT NULL,
    PaymentStatus VARCHAR(20) NOT NULL,
    AmountUSD     DECIMAL(10,2) NOT NULL,
    PaidAt        DATETIME2 NULL
);

INSERT INTO #PaymentsStage (PaymentID, BookingID, PaymentStatus, AmountUSD, PaidAt)
SELECT PaymentID, BookingID, PaymentStatus, AmountUSD, PaidAt
FROM dbo.Payment;

-- Add an index after loading
CREATE INDEX IX_PaymentsStage_BookingID ON #PaymentsStage (BookingID);

-- Now joins/grouping by BookingID become faster
SELECT BookingID, COUNT(*) AS Attempts
FROM #PaymentsStage
GROUP BY BookingID;

4B) Table variables: limited indexing pattern

Table variables can have a PRIMARY KEY or UNIQUE constraint, which creates an index,
but they are less flexible than temp tables for ad-hoc indexing decisions.

DECLARE @PaymentsStage TABLE
(
    PaymentID     INT NOT NULL PRIMARY KEY,
    BookingID     INT NOT NULL,
    PaymentStatus VARCHAR(20) NOT NULL,
    AmountUSD     DECIMAL(10,2) NOT NULL,
    PaidAt        DATETIME2 NULL
);

INSERT INTO @PaymentsStage (PaymentID, BookingID, PaymentStatus, AmountUSD, PaidAt)
SELECT PaymentID, BookingID, PaymentStatus, AmountUSD, PaidAt
FROM dbo.Payment;

SELECT BookingID, COUNT(*) AS Attempts
FROM @PaymentsStage
GROUP BY BookingID;

DBA guidance: If you need to add multiple indexes based on how you use the staged data,
temp tables are usually the better tool.

5) Practical DBA Use Cases (When to Choose What)

Choose a Temp Table when:

• You expect more than a small number of rows (dozens/hundreds/thousands or unknown scale).
• You will join the staged results to other tables.
• You need better query plans and statistics-based optimization.
• You may need to add indexes after loading data.
• You want to stage data in phases (load → cleanse → dedupe → summarize).
• You are writing incident scripts that must run safely under pressure.

Choose a Table Variable when:

• You have a small in-memory list (often a handful of IDs).
• You are doing simple logic without heavy joins/aggregations.
• You want a clean scope inside a stored procedure without needing explicit DROP.
• You want to pass a small “working set” inside a single batch.

DBA reality: Temp tables are used more often in performance-sensitive operational work.
Table variables are used more often for small helper sets.

6) DBA Safe Pattern: Stage IDs First, Then Act

One of the most professional DBA patterns is:

1. Stage the exact target keys into a temp table
2. Review them
3. Then perform updates/deletes using a join to that staged list

This reduces the risk of changing rows you did not intend to change.

Example: Stage target bookings, review, then update safely

-- 1) Stage target booking IDs
CREATE TABLE #Target
(
    BookingID INT NOT NULL PRIMARY KEY
);

INSERT INTO #Target (BookingID)
SELECT b.BookingID
FROM dbo.Booking AS b
WHERE b.Status = 'NEW'
  AND b.PickupAt < DATEADD(HOUR, -24, SYSDATETIME()); -- example condition

-- 2) Review the exact targets (DBA safety step)
SELECT b.BookingID, b.ConfirmationCode, b.Status, b.PickupAt
FROM dbo.Booking AS b
JOIN #Target AS t
  ON t.BookingID = b.BookingID
ORDER BY b.PickupAt ASC;

-- 3) Apply change using join to target list (prevents accidental wide updates)
BEGIN TRAN;

UPDATE b
SET b.Status = 'EXPIRED'
FROM dbo.Booking AS b
JOIN #Target AS t
  ON t.BookingID = b.BookingID;

-- Validate impact
SELECT b.BookingID, b.Status
FROM dbo.Booking AS b
JOIN #Target AS t
  ON t.BookingID = b.BookingID;

-- COMMIT TRAN;
-- ROLLBACK TRAN;

DROP TABLE #Target;

DBA note: This is safer than writing a complex WHERE clause directly in the UPDATE.

7) Temporary Objects and tempdb (Why DBAs Care)

Both temp tables and table variables use tempdb resources in SQL Server.
In real production systems, tempdb is one of the most common bottlenecks (especially under heavy workloads).

DBAs therefore follow these practical rules:

• Do not create unnecessary large temp objects.
• Only select the columns you need into temp storage.
• Drop temp tables when you are done (especially in long scripts).
• Index temp tables when it materially improves join/aggregate performance.
• Keep incident scripts short and predictable.

8) Common Mistakes (And How to Avoid Them)

Mistake A: Using a table variable for large datasets

This often causes slow plans due to row estimation issues. If you might have a lot of rows, use a temp table.

Mistake B: Forgetting that temp tables persist in the session

If you rerun scripts in the same session, you can hit “object already exists” errors.
DBAs often include a safe drop check:

IF OBJECT_ID('tempdb..#Target') IS NOT NULL
    DROP TABLE #Target;

Mistake C: Using SELECT INTO without controlling data types

SELECT INTO can be convenient, but DBAs prefer explicit CREATE TABLE for predictable types and constraints.
SELECT INTO can produce unexpected types/lengths.

9) Practice (Job-Ready Exercises)

Do these exercises in your lab:

1. Create #TargetBookings temp table containing BookingID for all bookings with Status = ‘NEW’.
   Review the staged rows by joining back to dbo.Booking.
2. Create an equivalent table variable and compare how you would use it in the same script.
3. Create #PaymentsStage and load payments, then create an index on BookingID and run a GROUP BY summary.
4. Build a safe “stage keys first, then update” pattern inside a transaction (COMMIT/ROLLBACK as practice).
5. Add the safe drop check for temp tables at the top of your script and rerun without errors.

Lesson Summary

• Temp tables (#) and table variables (@) are both used to store intermediate results for DBA scripts.
• Temp tables usually perform better for larger/unknown row counts due to better optimization and indexing flexibility.
• Table variables are best for small working sets and simple logic with limited joins.
• DBA-grade pattern: stage target keys first, review them, then apply changes using a join.
• Both use tempdb, so DBAs keep temp objects lean, indexed when necessary, and cleaned up.

Next Lesson: Lab — Build “Report Queries” Against a Sample Database (AdventureWorks)

Next, you will apply everything from Section 3 in a realistic environment using a sample database.
You will write SELECT queries, joins, aggregates, safe filters, and staging patterns to create practical DBA-style reports.