DEV Community

[Comment from a deleted post]

Replies for: 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 CT...

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...