SQL Tips by Namwar Rizvi

June 11, 2007

Table variables are actually created in tempdb not in memory

Filed under: Information,Performance,Query,SQL Server 2005,tips,TSQL — namwar @ 8:53 PM

There is a big misunderstanding about Table variables that they are just memory created in memory and have no physical footprint in tempdb as compared to their temporary tables. Wrong, they are not created in memory, they are created in tempdb. To prove this, run the following script which will show you the current list of temporary tables in your tmpdb and then will create a temporary variable. After this, it will again show you the current list of tables in tempdb. You will yourself see that your newly created temporary table variable is there. Please restart your server, if possible before running this script

–Restart your SQL Server, if possible, to clean the tempdb

–Switch context to Temdb
Use tempdb
Go
–Retrieve list of current temporary tables in Temp DB
Select TABLE_NAME From INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ‘%#%’
ORDER by Table_Name ;
GO

—Create a dummy table variable
Declare @dummyTable Table(col1 int);
Insert into @dummyTable values(1);

–Again run the query to find out how many table we have now
Select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME Like ‘%#%’
Order by Table_Name ;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: