Temporary objects

Question

Temporary objects

Viewing 5 posts - 16 through 19 (of 19 total)

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

BudaCli Hall of Fame Points: 3404 More actions · Answer 1

Good one, didn't know that the ## is for the global temp object, I thought tat this was only for global temp tables...Good one though.

What you don't know won't hurt you but what you know will make you plan to know better

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Just one question: why does the question say use two separate instances of SSMS or QA, and not two separate windows in one instance? The behaviour should be identical in the two cases - if it isn't, something somewhere is broken, isn't it?

Tom

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Tom.Thomson
the question say use two separate instances of SSMS or QA, and not two separate windows in one instance

If you have extensively followed the moans/groans and complaints associated with some of the QOD's (And I for one, at times have been slammed without mercy) then you might understand. The QOD is meant as a learning tool and I have learned from many, many comments NOT to leave a single wee little item open for interpretation.

I wanted to leave no, nada, nothing as a loop hole for gripes about the question being miss worded. Defensive on my part - Yes.

If you read some of the other posts on the forum I believe you will find one or two individuals who did test with one instance with two windows, no matter it still performs properly.

I hope this answers your question. If not post again to this forum

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 4

bitbucket-25253 (2/9/2010)
I wanted to leave no, nada, nothing as a loop hole for gripes about the question being miss worded. Defensive on my part - Yes.

That's an good and clear answer! Thanks. I've seen some of those gripes - I've even written some of them, but I hope that the ones I wrote were justified. I've seen several that were not.

Tom

rtelgenhoff SSC Eights! Points: 928 More actions · Answer 5

Now close the 2nd SSMS window that the inserts 2 & 3 were executed in and rerun the SELECT from the 1st/create window. You'll notice that it still returns a count of 3. Now open a new 2nd window and just put the select statement in there and run it. It returns a count of 3.

Last, close the 1st window where the table was created from and run the SELECT from the 2nd window. You'll now get an error 'Invalid object name ##Test'. That's because the global temp table is in scope as long as the connection that created the table is open and any other connections are 'actively' using the table, _meaning_ not that the 2nd connection is just open, but the SELECT statement is physically running at the time. If you close the creating connection while no other connections are actively using it, the table will be dropped. Likewise, if you close the creating connection and a 2nd connection finishes it's active use, it will be dropped and the 2nd connection can no longer access it.