Tuesday, December 15, 2009

Yes, Table Variables and Temp Tables both use the tempdb

Pinal Dave recently wrote a blog post called SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth which basically said that he often hears that Table Variables are stored in memory and Temp Tables are stored in the tempdb. He went on to show an example by loading rows into both and showing the page count. He also asked if anyone knew of another method to prove that both Temp Table and TableVariable are created in tempdb.

I found this post interesting, because it seems like I have answered that same questions at least a few times in the last couple of weeks. So here is my take on it. The best way for me to prove that they both reside in the tempdb (other than pointing to this kb article) is just to look for it there.

The Code

Let’s take a look at the code we can use to look for the objects in the tempdb.

  1. Create a Table Variable and immediately insert the current time.
  2. Wait ten seconds and then create a Temp Table.
  3. Select the values inserted into the tables.
  4. Look for the objects in the tempdb.
  5. Cleanup by dropping the Temp Table.
--1
DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO @TableVariable DEFAULT VALUES

--2
WAITFOR DELAY '00:00:10'

CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO #TempTable DEFAULT VALUES

--3
SELECT DT AS TableVariableTime FROM @TableVariable
SELECT DT AS TempTableTime FROM #TempTable

--4
SELECT * FROM tempdb.sys.objects
WHERE type = 'U'

--5
DROP TABLE #TempTable

You can see the output of the preceding code in the following image.

image

As you can see the TableVariableTime corresponds to the object created in the tempdb named #73BA3083, which is just hexadecimal of its object_id 1941581955, and the TempTableTime corresponds to the #TempTable object created ten seconds later. For space reasons, I didn’t include the entire object name for the Temp Table, but each object that gets created will have unique name as well.

Just for Fun

Now that I know I have objects in the tempdb, what do you think will happen if I try to manipulate them by the name in the tempdb instead of the names we gave them? Well, one of the first things I learned when I started working with SQL is that if I know the name of a table I should be able to insert some data. I can get the name of the table that exists while I am running the code and execute the INSERT statement using dynamic SQL by using the following code.

DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)

DECLARE @SQL VARCHAR(1000)
DECLARE @Name VARCHAR(50)

SELECT @Name = name 
FROM tempdb.sys.objects
WHERE type = 'U' AND create_date > DATEADD(s,-2, GETDATE()) 

SET @SQL = 'INSERT INTO tempdb.dbo.[' + @Name + '] DEFAULT VALUES'
PRINT 'Table Name: ' + @Name
PRINT ''
PRINT 'Query: ' + @SQL
PRINT '' 

EXEC (@SQL)

Ehh. Wrong Answer.

image

You can see in the preceding image that SQL Server ignores the request and generates an error. So, while the objects exist, and you even know the name, the only way to interact with them is through the front end.

0 comments: