Temporary Tables

Question

Temporary Tables

franzambesten

SSCommitted

Points: 1693
More actions
December 26, 2007 at 6:19 am

#177804

Hi, guys, if I have a stored procedure that creates a temp #table inside the definition of it and someone uses/execute the stored procedure what happens when another user uses the same stored procedure, I understand that for each user exists a session,
but in the case of global temp ##table
what's about?

Viewing 6 posts - 1 through 5 (of 5 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

global temp tables will be visible to all sessions. You might need global temp tables only when multiple sessions need to insert/update/delete data from the same temp teble.

.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

I'd avoid these if you can. If you really need to store something, use a permanent table and store it in there. Even if you link it to a SPID or login ID with a column to track that, it's easier to share data this way than with temp tables.

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

I agree with Steve in avoiding ## Global Temp Tables... if two procs (or the same proc running more than once) attempt to create a GTT (Global Temp Table) of the same name at the same time... BOOM!

--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)

franzambesten SSCommitted Points: 1693 More actions · Answer 4

Great Jeff! Bingo! I refered about that, it was my doubt, thank you for your answers guys!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

You're welcome... thank you for the feedback.

--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)