(scottish) sql bob blog

rss

Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.


Exploring Temporary Objects in SQL Server
There are many mysteries in life fortunately there are few about table variables.  That said when I look at other people's code, or when I hear their opinions about Table Variables, there is some mis-understandings about Table variables.  Let's be clear I love table variables, like many things in life if used correctly they are amazing.  It's easy to misuse something especially if we make assumptions and yes, I am a pointing a finger at myself.  

When I talk to people about table variables one of the items of advice that I give them, is that table variables are NOT created in memory.  Just like temporary tables are created in TempDB.  There might be some people reading this which disagree with me.  So, let's run some code. 

/*Create a table variable, with a column name which is kind of unique*/ 
DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT); 

/*Place some values into the table variable, just so it has something   
In the table variable*/ 
INSERT INTO @TestTableVariable 
VALUES(1); 
INSERT INTO @TestTableVariable 
VALUES(2); 
DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT); 
IF EXISTS 
(
    SELECT * 
    FROM tempdb.sys.tables AS T 
         INNER JOIN tempdb.sys.columns AS C ON T.Object_ID = C.Object_ID 
    WHERE C.Name = 'ID_VerySpecialColumnNameForThisBlog' 

    BEGIN 
        SELECT 'Found it, look here -> ',  
               T.Name,  
               C.Name,  
               C.column_id 
        FROM tempdb.sys.tables AS T 
             INNER JOIN tempdb.sys.columns AS C ON T.Object_ID = C.Object_ID 
        WHERE C.Name = 'ID_VerySpecialColumnNameForThisBlog'; 
   END; 
ELSE 
    BEGIN 
        SELECT 'Opps cannot find temp table with a column name of [ID_VerySpecialColumnNameForThisBlog] my bad'; 
    END; 

So first the code created a table variable, when the table variable was created, the name of the column was rather unique (there is a reason for that).  Next there was some records inserted into the table variable, just for a bit of fun. 

So the query in the EXISTS() just checks to see if there is a table created in TempDB and one of the columns has a name exactly the same as the one in the table variable created.  This shows that the Table Variable is created in TempDB, just like a temporary table.  Maybe you still do not believe me?  Well maybe this from Microsoft will clarify the point  

A4: 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).”