T-SQL temporary tables: #local and ##global

A procedure builds #made_inside, fills it with 3 rows and returns, and the caller’s next SELECT from #made_inside fails with error 208, “Invalid object name”. The same caller can create #stage first, and every procedure it calls reads all 3 of its rows. A #local temporary table lives in tempdb for as long as the scope that created it, and that one rule decides which code can see it; a ##global one is shared by every session of the instance.

You will predict who can see a #local and a ##global table, see why temporary tables have statistics and indexes like ordinary ones, control the columns that SELECT INTO creates, learn what a ROLLBACK does to their rows, and stage, clean and apply a messy feed with them.

A #table lasts as long as the scope that created it

A table whose name starts with # is a local temporary tablelocal temporary table: A table whose name starts with one #, kept in tempdb for the session that created it and dropped when its procedure or its session ends; procedures it calls can use it.: it lives in tempdbtempdb: The SQL Server system database that holds temporary tables, table variables and the work tables of sorts and spools; it is created again, empty, every time the instance starts., only the session that created it can use it, and SQL Server drops it when its scope ends. Microsoft’s CREATE TABLE page gives the two scopes. A #table that a stored procedure creates goes away as soon as the procedure ends; the procedures it calls can use the table, but the code that called the procedure cannot. Every other #table, such as one created by a batch you type in sqlcmd, lasts until the session ends or a DROP TABLE removes it. Step through the lab script:

The script behind those steps creates both procedures and #stage, and its comments give what each statement prints:

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd without -b.
-- Who can see a local temporary table: a #table created by the caller is visible to the procedures it calls, and a
-- #table created inside a procedure is dropped when that procedure ends. Expected output in the comments.
SET NOCOUNT ON;
DROP TABLE IF EXISTS #stage;
GO
CREATE OR ALTER PROCEDURE dbo.count_stage
AS
BEGIN
  SET NOCOUNT ON;
  SELECT COUNT(*) AS rows_seen_by_procedure FROM #stage;            -- 3: the caller's table
END;
GO
CREATE OR ALTER PROCEDURE dbo.make_inside
AS
BEGIN
  SET NOCOUNT ON;
  CREATE TABLE #made_inside (sku varchar(12) NOT NULL);
  INSERT INTO #made_inside (sku) SELECT sku FROM dbo.stock;
  SELECT COUNT(*) AS rows_inside FROM #made_inside;                 -- 3
END;                                                                -- #made_inside is dropped here
GO
CREATE TABLE #stage (account_id int NOT NULL, balance decimal(14,2) NOT NULL);
INSERT INTO #stage (account_id, balance)
SELECT account_id, balance FROM dbo.accounts WHERE customer_id = 1 OR account_id = 3;   -- accounts 1, 2 and 3
EXEC dbo.count_stage;                                               -- 3
EXEC dbo.make_inside;                                               -- 3
GO
SELECT COUNT(*) AS rows_after_return FROM #made_inside;             -- error 208: Invalid object name '#made_inside'.
GO
SELECT COUNT(*) AS still_here FROM #stage;                          -- 3: it lasts until the session ends
DROP TABLE #stage;
DROP PROCEDURE dbo.count_stage, dbo.make_inside;
GO

"""temp_scope.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("scope/temp_scope.sql", errors=1)

$ sqlcmd -S localhost -U sa -C -d lab -i scope/temp_scope.sql
rows_seen_by_procedure
----------------------
                     3
rows_inside
-----------
          3
Msg 208, Level 16, State 1, Server lab-mssql, Line 1
Invalid object name '#made_inside'.
still_here
----------
         3

Procedures that use the caller’s table compile even though #stage does not exist when they are created: the name is resolved when the statement runs. Because several sessions may run the same procedure at once, SQL Server adds a unique suffix to each local temporary table’s name inside tempdb, which is why such names may have at most 116 characters instead of 128.

A name that starts with ## makes a global temporary tableglobal temporary table: A table whose name starts with ##, kept in tempdb and shared by every session of the instance until the session that created it ends and no statement still uses it., which every session on the instance can read and change. The table compares the two kinds:

The dropping rule for ## tables holds while the database-scoped option GLOBAL_TEMPORARY_TABLE_AUTO_DROP is on, its default. SQL Server reads that option from tempdb, so setting it in a user database changes nothing; with it off, a global table stays until someone drops it or the instance restarts.

Temp tables have statistics, indexes and recompiles like other tables

A temporary table is a real table in tempdb, so it can have a primary key, unique and check constraints and indexes, and SQL Server keeps statisticsstatistics: What SQL Server records about the values in a column or index, such as how many rows hold each range of values, so the optimizer can estimate how many rows a step of a query returns. on its columns as it does for any table. The optimizer uses those statistics to estimate how many rows each step of a query returns, which is what lets it choose a good execution planexecution plan: The steps SQL Server compiles for running a batch or a query, such as which indexes to read and in which order to join tables; the optimizer chooses them before the statements run. More in “T-SQL batches, GO and variables” for a join with a large staged set. Microsoft’s tempdb page adds what makes them cheap: operations in tempdb are minimally logged, temporary tables are cached so that creating and dropping them is fast, and tempdb is created afresh every time SQL Server starts. SQL Server 2025 adds two tempdb options: accelerated database recovery, which once enabled (it takes a restart) rolls back work on temporary tables at once, and space resource governance, which caps how much of tempdb one workload may fill.

Statistics have a cost too. When enough rows of a table change, its statistics are marked out of date, and the next query that uses them updates them and is compiled again. Microsoft’s Statistics page gives the thresholds for compatibility level 130 and later, and a temporary table with fewer than 6 rows has a threshold of its own. This model prints them for five table sizes; press Edit, add 50 to SIZES, and run it again:

"""How many changes make the statistics of a table out of date: the thresholds on Microsoft's Statistics page.

From SQL Server 2016 on, at database compatibility level 130 and later, a temporary table with
fewer than 6 rows is out of date after 6 changes, and any table of up to 500 rows after 500.
Above 500 rows the threshold is MIN(500 + 0.20 * n, SQRT(1000 * n)). The next query that uses
statistics marked out of date updates them and is compiled again. Edit SIZES and run it.
"""

import math

SIZES = [3, 100, 500, 20_000, 2_000_000]


def threshold(rows, temporary):
    if temporary and rows < 6:
        return 6
    if rows <= 500:
        return 500
    return min(500 + 0.20 * rows, math.sqrt(1000 * rows))


print(f"{'rows':>11}  {'temporary table':>15}  {'permanent table':>15}")
for n in SIZES:
    temp, perm = threshold(n, True), threshold(n, False)
    print(f"{n:>11,}  {temp:>15,.0f}  {perm:>15,.0f}")

       rows  temporary table  permanent table
          3                6              500
        100              500              500
        500              500              500
     20,000            4,472            4,472
  2,000,000           44,721           44,721

A temporary table of 3 rows is out of date after 6 changes, where a permanent one waits for 500. Above 500 rows both kinds use the smaller of two numbers, 500 plus a fifth of the rows or the square root of 1,000 times the rows: 4,472 changes for 20,000 rows and 44,721 for 2 million, where the square root is the smaller. A procedure that fills a small #table row by row can therefore compile some of its statements again during the run, which is the price of the good estimates.

SELECT INTO copies each column’s type and nullability

There are two ways to make a temporary table. CREATE TABLE #t (…) names every column, its type, its nullability and its constraints before any row arrives. SELECT … INTO #t FROM … creates the table from a query and fills it in one statement; Microsoft’s SELECT INTO page says that each new column has the same name, data type, nullability and value as its expression in the select list. A plain identity column even brings its IDENTITY property along, unless the query has a join or a UNION, or the column is part of an expression, listed twice or read from a remote data source. Indexes, constraints and triggers are never copied, so the #acct below has no primary key until you create one. The script copies dbo.accounts and asks tempdb what it created:

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd.
-- SELECT ... INTO #acct takes each column's name, type and nullability from the select list, and the IDENTITY property
-- of a plain identity column. tempdb.sys.columns shows what SQL Server derived. Expected values in the comments.
SET NOCOUNT ON;
DROP TABLE IF EXISTS #acct;
SELECT account_id,                                                      -- int NOT NULL, IDENTITY inherited
       balance,                                                         -- decimal(14,2) NOT NULL, as in dbo.accounts
       balance * 0.04                                   AS interest,    -- nullable: the result of an expression
       ISNULL(CAST(balance * 0.04 AS decimal(14,2)), 0) AS interest_fixed   -- decimal(14,2) NOT NULL, as asked for
INTO #acct
FROM dbo.accounts;
SELECT c.name, TYPE_NAME(c.user_type_id) AS type_name, c.precision, c.scale, c.is_nullable, c.is_identity
FROM tempdb.sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'tempdb..#acct')
ORDER BY c.column_id;
SELECT COUNT(*) AS rows_copied FROM #acct;                              -- 4
DROP TABLE #acct;
GO

"""select_into_types.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("types/select_into_types.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i types/select_into_types.sql
name           type_name precision scale is_nullable is_identity
-------------- --------- --------- ----- ----------- -----------
account_id     int              10     0           0           1
balance        decimal          14     2           0           0
interest       numeric          17     4           1           0
interest_fixed decimal          14     2           0           0
rows_copied
-----------
          4

account_id and balance arrive exactly as dbo.accounts defines them, NOT NULL included. balance * 0.04 becomes a nullable column, because SQL Server treats the result of most expressions as nullable even when every input is NOT NULL, and its precision follows the rules for multiplying decimals. ISNULL(CAST(… AS decimal(14,2)), 0) fixes both: CAST chooses the type and ISNULL with a value that is never NULL makes the column NOT NULL. The table sums up when each way of creating the table fits:

A ROLLBACK undoes temp-table rows, and foreign keys are skipped

Temporary tables take part in transactions like other tables. Rows inserted into a #table inside a transaction are removed by its ROLLBACK, which is the difference from table variables that the next lesson uses on purpose. Constraints work as on other tables with one exception, which Microsoft’s CREATE TABLE page states: a FOREIGN KEY on a temporary table is skipped with a warning and never enforced, and no table may reference a temporary table either. The script shows the rollback and the skipped key:

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd without -b.
-- Temporary tables take part in transactions: a ROLLBACK removes the rows inserted into #pending. A FOREIGN KEY on a
-- temporary table is skipped with a warning, so nothing checks it. Expected output in the comments.
SET NOCOUNT ON;
DROP TABLE IF EXISTS #pending, #ledger_copy;
CREATE TABLE #pending (account_id int NOT NULL PRIMARY KEY, amount decimal(14,2) NOT NULL);   -- an unnamed key
BEGIN TRANSACTION;
  INSERT INTO #pending (account_id, amount) VALUES (1, 250.00), (3, -40.00);
  SELECT COUNT(*) AS rows_inside_the_transaction FROM #pending;   -- 2
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS rows_after_rollback FROM #pending;             -- 0: the rows were part of the transaction
GO
CREATE TABLE #ledger_copy (
  entry_id   int NOT NULL PRIMARY KEY,
  account_id int NOT NULL REFERENCES dbo.accounts (account_id)    -- warning 1756: the FOREIGN KEY definition is skipped
);
INSERT INTO #ledger_copy (entry_id, account_id) VALUES (1, 99);   -- accepted: account 99 does not exist
SELECT COUNT(*) AS rows_nothing_checked FROM #ledger_copy;        -- 1
DROP TABLE #pending, #ledger_copy;
GO

"""temp_rollback.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("rollback/temp_rollback.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i rollback/temp_rollback.sql
rows_inside_the_transaction
---------------------------
                          2
rows_after_rollback
-------------------
                  0
Skipping FOREIGN KEY constraint '#ledger_copy' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.
rows_nothing_checked
--------------------
                   1

The first SELECT counts 2 rows inside the transaction and the second counts 0 after the ROLLBACK. The second batch creates #ledger_copy with a reference to dbo.accounts, gets message 1756, “Skipping FOREIGN KEY constraint … definition for temporary table”, and then accepts a row for account 99, which does not exist.

Constraint names need care of their own. The suffix that keeps two sessions’ #stage tables apart is added to table names only, while a constraint name must be unique within its table’s schema, and every temporary table is created in tempdb’s dbo schema. A name you write yourself is therefore one name for every session of the instance:

Scripts that create temporary tables start with DROP TABLE IF EXISTS #t;, so they can run again in a session that still holds the table from the last run.

Stage, clean and apply a feed with temp tables

Temporary tables earn their place when work happens in steps that each need the result of the last. A supplier sends 6 stock rows for the practice shop: the tea price is corrected later the same day, the rice arrives once as RICE-5K and once as rice-5k with a trailing space, and a new item, toor dal, is sent twice. Step through the script’s plan:

The script keeps the raw feed in #feed, builds #latest with one row per cleaned SKU by keeping the row that ROW_NUMBER() numbers 1 from the newest time, puts a unique index on it, and then updates and inserts into dbo.stock in one transaction:

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd.
-- Stage, clean and apply a messy stock feed with temporary tables: load #feed, keep the latest row per cleaned SKU in
-- #latest, update and insert into dbo.stock, and report. dedupe_model.py prints the same rows. The ROLLBACK at the end
-- keeps the practice data as it was loaded.
SET NOCOUNT ON;
DROP TABLE IF EXISTS #feed, #latest;

-- Step 1: the feed as it arrived.
CREATE TABLE #feed (
  sku         varchar(12)   NOT NULL,
  item_name   nvarchar(60)  NOT NULL,
  unit_price  decimal(10,2) NOT NULL,
  on_hand     int           NOT NULL,
  received_at datetime2(0)  NOT NULL
);
INSERT INTO #feed (sku, item_name, unit_price, on_hand, received_at) VALUES
  ('TEA-250',  N'Assam tea 250 g',        180.00, 55, '2026-03-15 09:00'),
  ('TEA-250',  N'Assam tea 250 g',        185.00, 52, '2026-03-15 17:30'),   -- a later correction
  ('RICE-5K',  N'Sona masoori rice 5 kg', 420.00, 25, '2026-03-15 09:00'),
  ('rice-5k ', N'Sona masoori rice 5 kg', 430.00, 30, '2026-03-16 08:00'),   -- the same SKU, typed differently
  ('DAL-1K',   N'Toor dal 1 kg',          160.00, 40, '2026-03-16 08:00'),   -- a new item
  ('DAL-1K',   N'Toor dal 1 kg',          160.00, 40, '2026-03-16 08:00');   -- sent twice

-- Step 2: one row per cleaned SKU, the latest one, in a table with a unique index.
SELECT sku, item_name, unit_price, on_hand
INTO #latest
FROM (
  SELECT UPPER(TRIM(sku)) AS sku, item_name, unit_price, on_hand,
         ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(sku)) ORDER BY received_at DESC) AS rn
  FROM #feed
) AS ranked
WHERE rn = 1;
CREATE UNIQUE CLUSTERED INDEX latest_sku ON #latest (sku);
SELECT sku, unit_price, on_hand FROM #latest ORDER BY sku;      -- DAL-1K 160.00 40, RICE-5K 430.00 30, TEA-250 185.00 52

-- Step 3: apply it in one transaction and report what changed.
BEGIN TRANSACTION;
  UPDATE s SET s.unit_price = l.unit_price, s.on_hand = l.on_hand
  FROM dbo.stock AS s JOIN #latest AS l ON l.sku = s.sku;
  PRINT CONCAT(N'updated: ', @@ROWCOUNT);                       -- 2
  INSERT INTO dbo.stock (sku, item_name, unit_price, on_hand)
  SELECT l.sku, l.item_name, l.unit_price, l.on_hand
  FROM #latest AS l
  WHERE NOT EXISTS (SELECT 1 FROM dbo.stock AS s WHERE s.sku = l.sku);
  PRINT CONCAT(N'inserted: ', @@ROWCOUNT);                      -- 1
  SELECT sku, unit_price, on_hand FROM dbo.stock ORDER BY sku;  -- DAL-1K, GHEE-500 untouched, RICE-5K, TEA-250
ROLLBACK TRANSACTION;                                           -- the practice data stays as loaded
DROP TABLE #feed, #latest;
GO

"""dedupe_feed.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("feed/dedupe_feed.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i feed/dedupe_feed.sql
sku     unit_price on_hand
------- ---------- -------
DAL-1K      160.00      40
RICE-5K     430.00      30
TEA-250     185.00      52
updated: 2
inserted: 1
sku      unit_price on_hand
-------- ---------- -------
DAL-1K       160.00      40
GHEE-500     420.00       0
RICE-5K      430.00      30
TEA-250      185.00      52

This model follows the same rows through the three steps; press Edit, change the time of the second tea row to "15 08:00", and run it again:

"""The stock feed of dedupe_feed.sql, staged, cleaned and applied: what the script's temporary tables hold.

Step 1 keeps the feed as it arrived (#feed). Step 2 cleans each SKU as UPPER(TRIM(sku)) does
and keeps the latest row per cleaned SKU, as ROW_NUMBER() OVER (PARTITION BY ... ORDER BY
received_at DESC) = 1 does (#latest). Step 3 updates the SKUs that dbo.stock already holds and
inserts the others. Edit FEED (add a row, change a time or a price) and run it again.
"""

STOCK = {  # dbo.stock as the practice schema loads it: sku -> (unit_price, on_hand)
    "TEA-250": ("180.00", 40),
    "RICE-5K": ("420.00", 25),
    "GHEE-500": ("420.00", 0),
}

FEED = [  # sku as sent, unit_price, on_hand, received (day and time, sortable)
    ("TEA-250", "180.00", 55, "15 09:00"),
    ("TEA-250", "185.00", 52, "15 17:30"),
    ("RICE-5K", "420.00", 25, "15 09:00"),
    ("rice-5k ", "430.00", 30, "16 08:00"),
    ("DAL-1K", "160.00", 40, "16 08:00"),
    ("DAL-1K", "160.00", 40, "16 08:00"),
]

print(f"step 1, #feed as it arrived: {len(FEED)} rows")

latest, seen = {}, {}
for sku, price, on_hand, received in FEED:
    key = sku.strip().upper()
    seen[key] = seen.get(key, 0) + 1
    if key not in latest or received > latest[key][2]:
        latest[key] = (price, on_hand, received)

print(f"step 2, #latest: {len(latest)} rows, the latest per cleaned SKU")
for key in sorted(latest):
    price, on_hand, _ = latest[key]
    print(f"  {key:<9} {price:>7} {on_hand:>4}   from {seen[key]} feed rows")

print("step 3, applied to dbo.stock")
updated = inserted = 0
for key in sorted(latest):
    price, on_hand, _ = latest[key]
    if key in STOCK:
        old_price, old_on_hand = STOCK[key]
        print(f"  {key:<9} update  {old_price} {old_on_hand} -> {price} {on_hand}")
        updated += 1
    else:
        print(f"  {key:<9} insert  {price} {on_hand}")
        inserted += 1
untouched = ", ".join(sorted(set(STOCK) - set(latest))) or "none"
print(f"updated: {updated}, inserted: {inserted}, untouched: {untouched}")

step 1, #feed as it arrived: 6 rows
step 2, #latest: 3 rows, the latest per cleaned SKU
  DAL-1K     160.00   40   from 2 feed rows
  RICE-5K    430.00   30   from 2 feed rows
  TEA-250    185.00   52   from 2 feed rows
step 3, applied to dbo.stock
  DAL-1K    insert  160.00 40
  RICE-5K   update  420.00 25 -> 430.00 30
  TEA-250   update  180.00 40 -> 185.00 52
updated: 2, inserted: 1, untouched: GHEE-500

Six rows become three: 185.00 for the tea, 430.00 and 30 bags for the rice, and the dal once. The feed updates 2 items, inserts 1 and leaves the ghee untouched. With the tea’s later row moved before the first one, 180.00 wins instead, which is why the order of a ROW_NUMBER() decides what “latest” means. Ties need a rule as well: the two dal rows share their time, so ROW_NUMBER() may number either of them 1, which is harmless only because they are identical; a feed whose tied rows differ needs a tie-breaker, such as a line number, at the end of the ORDER BY. The script’s last statement rolls the changes back, so the practice data stays as loaded.

Practise: predict what each #table holds

Each question shows a few statements around a temporary table. Decide who can see it, what it contains and when it goes away, then read why each answer is right or wrong:

Interview questions

Warm-up (fresher to 2–5 years): how do #local and ##global temporary tables differ?

Both live in tempdb and are dropped automatically, but they differ in who can use them and when they go. A #local table belongs to the session that created it: a procedure’s #table is dropped when the procedure ends and can be used by the procedures it calls, and any other #table lasts until the session ends. Two sessions that create #stage get two separate tables, because SQL Server adds a unique suffix to the name in tempdb. A ##global table is one table for every session on the instance; it is dropped when the session that created it has ended and no statement still uses it. Global tables suit a short hand-over between sessions; most code needs the isolation of #local tables.

Two longer questions follow, one about a report procedure that stops running in parallel and one about a scheduled job whose second step cannot find its staging table; work out your answer before you open each first step:

Key takeaways

• A #table created in a procedure is dropped when the procedure ends and is visible to the procedures it calls; any other #table lasts until its session ends, and a ##table is shared by every session.
• Temporary tables live in tempdb with statistics, constraints and indexes, so the optimizer estimates them well, and a small one is marked out of date after only 6 changes.
• SELECT INTO copies names, types and nullability from the select list, so CAST and ISNULL decide the columns it creates.
• A ROLLBACK removes rows inserted into a temporary table, its foreign keys are never enforced, and its other constraints stay unnamed, because a constraint name gets no suffix and is shared by every session in tempdb.
• Staging the raw feed, de-duplicating into a second table and applying it in one transaction keeps every step checkable.