Table variables versus #temp tables:
- They are only in memory?
- Don’t use tempdb space?
- Better for small result sets?
- Used for data modification you go serial
(Reason: TableVariableTransactionsDoNotSupportParallelNestedTransaction) - The don’t have statistics.
Are you sure of that last one Mr. DBA?
DECLARE
@t table -- A Table Variable
(
id integer NOT NULL,
INDEX c CLUSTERED (id)
);
INSERT
@t
(
id
)
SELECT
p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656;
SELECT
lmao = COUNT_BIG(*)
FROM @t AS t
WHERE t.id = 22656;
GO
/* Look at the saved plan for this one. */
DECLARE @t table
(
id integer NOT NULL,
INDEX c CLUSTERED (id)
);
INSERT @t
(id)
SELECT x.id
FROM
(
SELECT
22656 /* Famous Jon Skeet */
UNION ALL
SELECT TOP (999)
u.Id /* Crappy ones */
FROM dbo.Users AS u
WHERE u.Reputation = 1
ORDER BY
u.CreationDate
) AS x (id);
SELECT
lmao = COUNT_BIG(*)
FROM @t AS t -- And used in the query
JOIN dbo.Posts AS p
ON p.OwnerUserId = t.id
JOIN dbo.Comments AS c
ON c.UserId = t.id;
GO
Checking the Properties the Estimates and Actuals are the same and even in Batch Mode. Ah.. Ronald you cheat because of the index.. nope I don’t.
DROP TABLE IF EXISTS #t;
CREATE TABLE #t
(
id integer NOT NULL,
--INDEX c CLUSTERED (id)
);
INSERT #t
(id)
SELECT x.id
FROM
(
SELECT
22656
UNION ALL
SELECT TOP (999)
u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 1
ORDER BY
u.CreationDate
) AS x (id);
SELECT
lmao = COUNT_BIG(*)
FROM #t AS t
JOIN dbo.Posts AS p
ON p.OwnerUserId = t.id
JOIN dbo.Comments AS c
ON c.UserId = t.id;
On SQL Server 2025 the lesson is always check the execution plan (the pure truth) and in Dutch we would say “Je ankers verplaatsen..” (if there is any water in our rivers left..(2026-08-12) sometimes question the things people tell you.