Friday, March 11, 2011

A table variable is not a memory-only structure


Yep, the word 'Variable' is misleading.















According to Microsoft article:

"A table variable is not a memory-only structure.
Because a table variable might hold more data than
can fit in memory, it has to have a place on disk to store data.
Table variables are created in the TempDB database similar
to temporary tables. If memory is available, both table variables
and temporary tables are created and processed while
in memory (data cache). "

We can easily check this by checking the 'sysobjects'
table in the TempDB:

DECLARE @TestTable TABLE(BookID INT)

SELECT TOP 1 * 
FROM tempdb.sys.sysobjects
WHERE [name] LIKE '#%'
AND [name] NOT LIKE '%[_]%'
ORDER BY crdate DESC

Thanks for reading.

No comments:

Post a Comment