Temp Table Existance

Question

Temp Table Existance

hefterr-972543

SSC Journeyman

Points: 91
More actions
December 22, 2008 at 8:40 am

#71119

Hi,
I a a newbie so please so I appologize if this is an obvious question.
How does one define the length or scope that a temp table (#mytemp) will exist. What is the definition of a SQL server "session" which is always what the documentation refers to as the scope of a temp table.
Can a program call a stored procedure multiple times in a row with different inputs and expect the temp table to persist?
Thanks in advance.

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

hefterr (12/22/2008)
Hi,
I a a newbie so please so I appologize if this is an obvious question.
How does one define the length or scope that a temp table (#mytemp) will exist. What is the definition of a SQL server "session" which is always what the documentation refers to as the scope of a temp table.
Can a program call a stored procedure multiple times in a row with different inputs and expect the temp table to persist?
Thanks in advance.

You don't really "define" the length or scope that the temp table exists. They persist until the end of a session. (Sorry couldn't resist 😛 )

At the conclusion of any stored procedure, your session is closed, and your temp tables are dropped. Running a stored procedure multiple times and needing the results would require a different process, such as a global temp table (double ##), a real table, a parent procedure that accepts an array or table of values, etc.

SQL server is optimized for set based programming, and running a stored procedure many times in a row to make up a result set is normally not a good design choice if it can be avoided. If you can explain your objective, we may be able to suggest a better method of doing it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ps. One Orange Chip Points: 29422 More actions · Answer 2

Just a point to make.

If You're using Create table #myTempTable (), it'd be dropped automatically when the session that created it is terminated.

If you're using SELECT * INTO #myTempTable FROM OtherTable.... It'd be dropped the moment execution comes out of the scope it's created in.

Pradeep Singh

hefterr-972543 SSC Journeyman Points: 91 More actions · Answer 3

Just wasn't sure what is the definision of the "session".

ps. One Orange Chip Points: 29422 More actions · Answer 4

hefterr (12/22/2008)
Just wasn't sure what is the definision of the "session".

Session - You write a query in a query window in SSMS, that's a connection to the server and a session alloted to the user who initiated the connection. The moment you close it, the connection is terminated and your temp table is dropped.

Pradeep Singh

hefterr-972543 SSC Journeyman Points: 91 More actions · Answer 5

Not sure how the session is defined from a Web program (using Coldfusion).

ps. One Orange Chip Points: 29422 More actions · Answer 6

i'm not sure abt coldfusion. all i know, each connection will initiate a user session at DB level. IF u've 10 browsers each connecting to the db, there will be 10 sessions.

Pradeep Singh