Table variable and it’s storage in tempdb

You know that temporary tables are stored in tempdb database although accessible to the current session only. Within a current session, it exists until you drop it.

Did you know table variable is also stored in tempdb database till the execution completes?

Consider the following code

DECLARE @NUMBERS TABLE(NUM INT)
SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES
SELECT TABLE_CATALOG ,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS

When you execute all the above, you can get some information about the table variables in the INFORMATION_SCHEMA views

It returns the following


-- Result1
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
--------------- -------------- ----------- -----------
tempdb dbo #AE4E2F08 BASE TABLE

— Result2
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME
————– ————– ———– ———–
tempdb dbo #B036777A NUM

If you look at the table name it starts with # like temporary variable but it is of hexadecimal number.

If you execute just the following

 

SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES
SELECT TABLE_CATALOG ,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS

You will not get the above results. It should be run simultaneously with table variable creation part. So table variables are stored in tempdb during the execution

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s