Apr 21, 2012

Storage of table variable

Table variables are stored in the tempdb database for execution scope only. 
Let us run this code

declare @t table(i int)
select from tempdb.INFORMATION_SCHEMA.TABLES  

The result is

tempdb               dbo           #BAB3F665        BASE TABLE

If your server does not use any temporary tables, you will get the above result only. As you see a table variable is stored in the tempdb database for the execution scope only just like a temporary table. The name is prefixed by #. But unlike a temporary table, the name does not contain underscores. Becauase they are stored in execution scope only, you cannot query the INFORMATION_SCHEMA.TABLES alone to get the table variable information. The following code will not show you any table vaiables


No comments:

Post a Comment


Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.