Table variables do get created in the TempDB

Id uniqueidentifier,
Name varchar(200)

INSERT INTO @Mytab1 VALUES (newId(),’Ashish’)
SELECT crdate AS CreatedDate,GetDate() As CurrentDate, Name from tempdb.dbo.sysobjects where name not like ‘sys%’


Execute the above query in any database multiple times with some seconds interval and you will see the CreatedDate and the CurrentDate very close to each other in the resultset. “CreatedDate” is the time when that table variable was created in the tempDB database and “CurrentDate” is the datetime when you executed the query. “CurrentDate” will always be greater (just milliseconds) than the “CreatedDate”.  Each time you execute the query, It creates a random name prefixed with a #  and there is no naming conflict and no error. A good article here.

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: