September 4, 2016 at 5:48 pm
Luis Cazares (9/1/2016)
Iwas Bornready (9/1/2016)
Derek Slinn (9/1/2016)
I think the answer is wrong,Both are stored in tempdb database.
technically table variables can just be in memory if there is only a small amount of data in them and it will page to tempdb if its gets bigger
That's what I thought.
I would say it depends. The data for both starts on RAM and when they grow it's thrown to disk.
However, their definitions are stored on tempdb since the beginning. Table variables are stored as a temp table with an eight character name preceded by #.
DECLARE @Date datetime = DATEADD( ms, -10, GETDATE());
DECLARE @MyTable TABLE(someid int);
SELECT *
FROM tempdb.sys.tables
WHERE create_date BETWEEN @Date AND DATEADD( ms, 20, GETDATE());
I wouldn't even put the "grow" into the equation. They can both be memory or disk based and I have seen very small of table variables (1 row) reside entirely on disk in tempdb.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2016 at 6:44 am
Interesting question and discussion.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply