https://sqlmaster.mssqlplanner.com

Tag: Temp Tables

  • Temp Table vs Table Variable vs Global Temp Table: A Decision Framework for SQL Server

    Temp Table vs Table Variable vs Global Temp Table: A Decision Framework for SQL Server

    Three temp object types, three genuinely different jobs. Now that you’ve built all three, here’s how to choose without guessing — and why this exact question shows up so often in interviews.

    Which temp object do you need?Need scratchdata?(start here)Table Variable (@)→ survives ROLLBACKsmall lookups, error logsLocal Temp Table (#)→ real stats + indexesDEFAULT PICKGlobal Temp Table (##)→ another session needs itlast resortno built-in isolationyou add locking yourselfsee previous lessonFolklore says tablevars are faster. Oftenbackwards — check realstats, not guesses. 📌

    The Decision Tree

    Survive a transaction rollback? Yes → Table Variable No → another session needs it? Yes → Global Temp Table No → Local Temp Table

    Situational Cheat Sheet

    Situation Right tool
    Staging a large intermediate result in a complex report Local temp table (#)
    Small lookup list of a few rows inside a procedure Table variable (@)
    Error/audit log that must survive a transaction rollback Table variable (@)
    Sharing a snapshot between two active debugging sessions Global temp table (##)
    Data needs real statistics for the optimizer to make good join choices Local temp table (#)
    You need to add an index only after seeing the shape of the data Local temp table (#) — table variables can’t be altered post-declaration

    This Is a Genuinely Common Interview Question

    “What’s the difference between a temp table and a table variable?” comes up constantly, precisely because the shallow answer (“table variables are smaller/faster”) is folklore, not fact — in practice, on non-trivial row counts, a table variable’s lack of real statistics can make it slower, not faster, exactly because the optimizer’s row estimate is wrong. The strongest interview answer isn’t a definition — it’s the rollback behavior from the previous lesson, because it’s the one difference that actually changes what your code does, not just how fast it runs.

    -- A one-line answer worth having ready: "table variables don't roll back with the
-- surrounding transaction, and historically carry no real statistics for the optimizer"
SELECT 'Table variable' AS type, 'Survives rollback, weak statistics' AS behavior
UNION ALL
SELECT 'Temp table', 'Rolls back with transaction, real statistics';
    Practice tip: Before moving to stored procedures in the next chapter, pick one real multi-step problem — even something simple like “top 3 highest earners per department” — and consciously decide, using this decision tree, which temp object type (if any) you’d use to solve it. Comparing your reasoning against a CTE-only solution is a useful check too: sometimes the right answer is none of the three.

    Enjoyed this?

    Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

  • Global Temp Tables in SQL Server (##temp): Sharing Data Across Sessions Safely

    Global Temp Tables in SQL Server (##temp): Sharing Data Across Sessions Safely

    A global temp table (##) is visible to every session on the server — genuinely useful, and genuinely a concurrency hazard if you’re not careful. This is the least commonly needed of the three temp object types, and this lesson is honest about exactly when that rarity is justified.

    Everyone can see it — nobody locks it##SharedDriverSnapshotone physical table, tempdbread + write, no isolationSession ASELECT / INSERTSession BSELECT / INSERTSession CSELECT / INSERTMeanwhile: two sessions raceSession A: IF NOT EXISTS(…) → FALSE, runs INSERTno lock held between the check and the insertSession B: IF NOT EXISTS(…) → FALSE too, runs INSERTsame instant — classic check-then-insert raceDUPLICATE ROWboth sessions inserted ‘Amir’##temp gives you NO isolation for free

    Creating and Sharing One

    CREATE TABLE ##SharedDriverSnapshot (
    driver_name NVARCHAR(100),
    total_fare  DECIMAL(10,2)
);

INSERT INTO ##SharedDriverSnapshot
SELECT driver_name, SUM(fare_usd) FROM dbo.TripAdvanced GROUP BY driver_name;

-- Any other session, connected to the same server, can now see this:
-- SELECT * FROM ##SharedDriverSnapshot;

DROP TABLE ##SharedDriverSnapshot;

    When It’s Dropped

    Dropped when: creating session ends AND no other session is still referencing it

    This second condition is the subtle part: if Session A creates a global temp table and disconnects, but Session B is mid-query against it, SQL Server keeps it alive until Session B finishes — it doesn’t get yanked out from under an active reader. Once every referencing session is done, it’s cleaned up automatically.

    The Concurrency Risk

    Multiple sessions can write to a global temp table simultaneously with no built-in isolation between them, unlike a real table where you’d deliberately design locking/transactions around it (Chapter 9 covers this properly). Two sessions inserting at the same time won’t corrupt data, but two sessions racing to both check-then-insert (“if this row doesn’t exist yet, add it”) can both pass the check simultaneously and both insert — a classic race condition, worse here because it’s easy to forget a scratch table needs the same concurrency discipline as a real one.

    -- A race condition waiting to happen if two sessions run this concurrently:
IF NOT EXISTS (SELECT 1 FROM ##SharedDriverSnapshot WHERE driver_name = 'Amir')
    INSERT INTO ##SharedDriverSnapshot VALUES ('Amir', 0);
-- Both sessions can see "not exists" before either has inserted, producing a duplicate

    Legitimate Use Cases

    Use it for genuinely useful, narrow cases — sharing a debug snapshot between two active SSMS windows during a troubleshooting session, or coordinating a multi-step batch/ETL job where separate connections (sometimes even separate tools) need to hand off an intermediate result. Reason explicitly about concurrent access rather than assuming it’s safe by default; in most cases, a permanent staging table with proper locking, or simply passing data through parameters, is the more robust choice for anything beyond ad-hoc debugging.

    Common mistake: Reaching for a global temp table as a lazy way to “share state between two parts of my application” instead of using a real table or a proper message-passing mechanism. It works in a demo and then causes an intermittent, hard-to-reproduce bug in production the first time two requests overlap.

    Enjoyed this?

    Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

  • Local Temp Tables in SQL Server (#temp): Scope, Statistics, and Real Use Cases

    Local Temp Tables in SQL Server (#temp): Scope, Statistics, and Real Use Cases

    Fundamentals Chapter 5 gave you a first look at local temp tables. This chapter goes further: the full scope rules, why they get real optimizer statistics (unlike table variables, next lesson), and where they genuinely earn their place over a CTE or subquery.

    Same name, separate copies(#temp tables are session-private)Session A#DriverTotalsreal stats + can be indexedSession B#DriverTotalsseparate physical copynever see each otherOuterProccreates #Sharedthen EXEC InnerProcInnerProcSELECT * FROM #Sharedinherited!Lifetime: dies whenthe outermost creatingscope ends (or DROP).📌

    Using One

    CREATE TABLE #DriverTotals (
    driver_name NVARCHAR(100),
    total_fare  DECIMAL(10,2),
    trip_count  INT
);

INSERT INTO #DriverTotals
SELECT driver_name, SUM(fare_usd), COUNT(*)
FROM dbo.TripAdvanced
GROUP BY driver_name;

-- Can be indexed, just like a real table
CREATE CLUSTERED INDEX IX_Temp_DriverName ON #DriverTotals (driver_name);

SELECT * FROM #DriverTotals WHERE total_fare > 30;

DROP TABLE #DriverTotals;

    Real Statistics, Real Indexes

    #temp behaves like a real table in tempdb Maintains real statistics → optimizer makes good decisions Supports CREATE INDEX after creation

    This is what makes local temp tables genuinely useful for staging large intermediate results in a complex report query — the optimizer isn’t flying blind the way it is with a table variable (next lesson makes this contrast concrete). SQL Server automatically creates and updates statistics on a #temp table’s data, exactly as it would for a permanent table, which means row-count estimates for anything querying it afterward are generally accurate.

    The Scope Rule, Precisely

    A local temp table created inside a stored procedure is visible to that procedure and anything it calls (nested procedures can see and use a caller’s #temp table — a genuinely useful pattern for passing staged data down a call chain), but disappears when the outermost creating scope ends. If two different sessions both create #DriverTotals, SQL Server silently gives them separate, isolated copies internally (renamed behind the scenes with a unique suffix) — they never collide, and neither session can see the other’s version.

    CREATE PROCEDURE dbo.OuterProc AS
BEGIN
    CREATE TABLE #Shared (id INT);
    INSERT INTO #Shared VALUES (1);
    EXEC dbo.InnerProc; -- InnerProc can see and use #Shared
END;
GO
CREATE PROCEDURE dbo.InnerProc AS
BEGIN
    SELECT * FROM #Shared; -- works: nested procs inherit the caller's temp tables
END;

    When a Temp Table Beats a CTE

    A CTE (covered in full in Chapter 6) is often the more elegant choice for a single query — but it’s re-evaluated wherever it’s referenced within that query, and doesn’t persist statistics or an index of its own. A local temp table earns its place specifically when: the same intermediate result is queried multiple times across several statements, the intermediate result is large enough that having a real index on it matters, or you need to break a genuinely complex multi-stage transformation into readable, individually-testable steps within a longer script or procedure.

    Common mistake: Reaching for a temp table out of habit for every intermediate step, even single-use ones inside one query, where a CTE would be simpler and equally fast. Temp tables have real overhead — tempdb I/O, statistics maintenance — that a CTE inside a single statement avoids entirely.
    Practice tip: Rebuild the nested-procedure example above yourself, then try querying #Shared from a completely separate session while OuterProc is still running — confirm it genuinely isn’t visible there, making the isolation concrete rather than theoretical.

    Enjoyed this?

    Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.