Table variables do get created in the TempDB

DECLARE @MyTab1 TABLE
(
Id uniqueidentifier,
Name varchar(200)
)

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

clip_image002

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.

Advertisements
  1. Leave a comment

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

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: