DEV Community

[Comment from a deleted post]

Replies for: It’s a common misperception that CTE queries are staged in memory or are “persisted data sets”. Not true. They’re simply syntactic sugar that SQL w...

Collapse
 
dirjel profile image
Devin Lamothe • • Edited

I made an account to say this. It's a really important distinction and will murder your performance sometimes.

I've seen the optimizer treat a CTE as a correlated subquery executed for every row in a nested loop execution plan.

CTE's are very easy to read, though. It is my default strategy for writing complex queries, and I'll only switch to #temptables if I see a performance issue with the CTE.

Collapse
 
t4rzsan profile image
Jakob Christensen • • Edited

Also, sometime SQL Server's in-memory table variables can be useful. They are limited by memory but fast and they require no special permissions (I think?).

DECLARE @employee TABLE
(
  EmployeeID INT PRIMARY KEY NOT NULL,
  Name NVARCHAR(20)
)

INSERT INTO @employee VALUES (1, 'Jakob')

SELECT * FROM @employee
 
sqlknowitall profile image
Jared Karney •

Table variables are not in memory only objects. They are logged in temodb. Table variables only last for execution and don't generate statistics.

 
t4rzsan profile image
Jakob Christensen •

Thanks for the clarification regarding tempdb. It seems that as of SQL Server 2017 you can create in-memory table variables.
docs.microsoft.com/en-us/sql/relat...