RSS

Are sql server table variables not being created in tempdb where as temp tables do?

23 Sep

Some TSQL developers and DBAs think that in SQL server table variables are created in SQL server memory where as only temporary tables created in tempdb. It is actually both these kind of temp tables are being created in tempdb. Actually you could do experiments to find out this.



SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )
GO
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

--create tamp Table with 1000000 recoreds
CREATE TABLE #TestTempTable ( Id INT )

INSERT  #TestTempTable
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
                
--see that temp table created in temp db                
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation               


GO
--see how much pages being in the database
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     ) 
GO 
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

SELECT  @DTBeforeCreation
                     
                     
--create table variable with same amount of records                     
DECLARE @TestTableVar TABLE ( Id INT )

INSERT  @TestTableVar
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
--see that table variable created in temp db database
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation  
GO                 
/*
see how much pages being in the database due to table variable creation
it proves that same amount of pages are being incremented used as in temp table creation
*/
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )    
   
                                  
                
/*Cleanup*/
DROP TABLE #TestTempTable

Here I am checking page allocation before and after temp table and table variable creation. It proves that in both occasions same amount of pages being increased since we inserted same amount of records to the two tables. I am also recording current date time before temp table and table variable creation and checking sys.objects in tempdb database to find tables that have been created after the time record. In both occasions make sure you are the only one connected to the target test database.
We can see that even after the table variable creation it is being recorded in tempdb database.

Advertisements
 
Leave a comment

Posted by on September 23, 2012 in MS SQL Server, SQL, TSQL

 

Tags: ,

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

 
%d bloggers like this: