Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Storage of table variable

Apr 9 2012 1:27AM by Madhivanan   

Did you know that table variables are stored in the tempdb database for execution scope only?. Ok. Let us run this code

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

The result is

TABLE_CATALOG       TABLE_SCHEMA   TABLE_NAME       TABLE_TYPE
---------------------------------------------------------------------
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

select * from tempdb.INFORMATION_SCHEMA.TABLES   

How many of you know this?

Tags: t-sql, sql_server, sqlserver, tsql, table_variable,


Madhivanan
3 · 40% · 12924
8
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

5  Comments  

  • Very nice, but what may be reason that I - using your example - do not have any records? Is that means that table can be stored at any other place?

    commented on Apr 10 2012 3:54AM
    Adam Tokarski
    58 · 3% · 1021
  • Adam, Did you run exactly the code shown above?

    commented on Apr 10 2012 5:15AM
    Madhivanan
    3 · 40% · 12924
  • Yes, I did.

    At one server (2008r2) I've got 14 rows, but at (2005) - that I'm working with - is
    br_missing_tv_1

    commented on Apr 10 2012 5:37AM
    Adam Tokarski
    58 · 3% · 1021
  • The initial understanding about table variable was that it only remains in memory and is not made in temporary database. I remember that I had read in begining that the table variable is only written to TEMPDB database when its size exceeds 2 MB otherwise it remains in memory.

    Since when did this logic changed???

    commented on Apr 26 2012 12:46AM
    chbahmed
    2038 · 0% · 8
  • I think this has changed from 2008 onwards. As you see from Adam's reply, it does not work in 2005

    commented on Apr 26 2012 1:41AM
    Madhivanan
    3 · 40% · 12924

Your Comment


Sign Up or Login to post a comment.

"Storage of table variable" rated 5 out of 5 by 8 readers
Storage of table variable , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]