difference in create temp table; using # vs. tempdb.dbo.table_name

Question

difference in create temp table; using # vs. tempdb.dbo.table_name

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Mel;

I think somewhere along the line, memory tables became pinned tables (DBCC PINTABLE).

(Sort of) Related, one of the early features of Windows 2000 Advanced Server was In-Memory DataBases (IMDBs). These were not SQL Server related, but available for applications for caching, and was going to be able to have SQL used on them. But it was dropped before release, promising that it was to be added as part of Application Center, but that never materialized either. I bring this up because just this past week, Roger Woltor on MSDN blogs just posted on why that never made it: http://blogs.msdn.com/rogerwolterblog/archive/2006/06/03/616571.aspx

Mark

mel-260965 SSC Enthusiast Points: 147 More actions · Answer 2

Hah! This forum is wonderful! I had hoped to meet others of my ilk. I love SS trivia. I guess I'll have to create a profile now.

You are right. The original term was "pinned table" but it was set up in the SQL Server's config file. I am not even sure they had DBCC back then. I am pretty sure they didn't.

Thanks again for the trip down memory lane, all puns intended.

...Mel

Jeff Moden SSC Guru Points: 1003911 More actions · Answer 3

For a lot more information on the differences between Temp Tables and Table Variables, see the following URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)