Temporary ( ) variable – A couple of points

Temporary ( ) variable – A couple of points

*The word between the parentheses is \’TABLE\’

It\’s been another one of those days when you realise that you don\’t really know as much as you think you do.

This time, it\’s table variables and temporary tables . You assume that temporary tables exist in the current database context, and that table variables exist only in memory.

Or at least, that\’s what I assumed. I was wrong. On both counts. They both exist in TEMPDB.(point 1)  But not exactly how you might think. (point 2)

Here\’s a small snippet you can use at home to prove what I\’m saying:

–Execute in ANY database context

CREATE TABLE #temp1
        (ID INT NULL)
       
DECLARE @temp1 TABLE
        (ID INT NULL)
       
SELECT * –< I know, but it's useful to compare the data
FROM tempdb..sysobjects
WHERE name LIKE \’#%\’

DROP TABLE #temp1

I\’ll step through this, if I may….

Firstly, pick a database context. Any database context will do, as long as you can execute SQL statements therein. 🙂

Then create the temporary table. (Note: There\’s nothing \’temporary\’ about temporary tables – they\’re real tables, and it\’s how you use them that makes them temporary)

Just for fun, skip the creation of the table variable and do the SELECT from tempdb..sysobjects. You don\’t have to include the WHERE, but it narrows things down a bit.

What you should see is an object that has a name a bit like the temporary table you just created, except it has some extra underscores and what looks like a hex value at the end. It\’s definatley your temp table, though. What\’s interesting about this is that SQL Server has to maintain an object reference somewhere to match up what you called the table, and what SQLServer called it in tempdb. Perhaps each of those references requires a bit of memory, so it\’s not quite as lightweight as you might think ? An ordinary table doesn\’t have these requirements.

Moving on, you\’d expect from this that the table variable you create would have a name of @temp1 (in the example), and possibly some underscores and a hex value at the end. Or is it just me that expected that ?

What you get when you create the table variable is….. well, you do it and see what you get…. Oh, you haven\’t got access to a SQLServer at the moment ? Right, so I\’ll tell you….

You get an object whose name starts with a \’#\’ sign, and eight characters representing a hexadecimal. Each time you create the table variable, you get a different hex vlaue, even if your definition is the same (I just got #6C190EBB and #6E01572D). This appears to be random (although you can never be sure with random).

What\’s REALLY interesting is that if you execute the script above several times, the hex value in tempdb for the temp table is INCREMENTAL. You start with 00000000, then 00000001, through 0000000A – F, 00000019, then 00000020, 000 021, and so on. Go on, give it a go. Even if you create the same temp table in another session, the hex value increments. THAT was unexpected !!!

Wait.

It gets better.

Change the name of the temporary table (and don’t forget the DROP change as well), and examine the name generated in tempdb. The first part of the name is your new table name, but the hex value at the end has incremented by one.

Go back to the first script and you\’ll see that the hash value has AGAIN incremented – even though you\’re referencing 2 different objects, it would appear that SQL is keeping count of the number of times a temporary table has been created.

And THAT, dear reader, is most interesting.

I wonder what happens when the 8 character hex value overflows….. But that\’s for another time, I think.

Back soon …