Common temp tables in procedures
-
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1
EXEC sp2
EXEC sp3
Job2
EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5
Job3
EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
Thanks.
-
Hey Sahana,
According to the T-SQL online reference in MSDN, it is recommended that table variables be used instead of temp tables whenever possible.
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp
One of the things I like about table variables is that I don't have to worry about dropping them after I have used them. SQL Server takes care of this.
To turn your temp table declaration into a table variable declaration, you change :
CREATE TABLE #temp (
...
)
<to>
DECLARE @temp TABLE (
...
)
You would also have to update all table references to the @temp variable as follows:
[ SELECT * FROM #temp ... ] would need to be changed to [ SELECT * FROM @temp ... ]
[ INSERT INTO #temp ... ] would need to be changed to [ INSERT INTO @temp ... ]
[ UPDATE #temp ... ] would need to be changed to [ UPDATE @temp ... ]
[ DELETE FROM #temp ... ] would need to be changed to [ DELETE FROM @temp ... ]
Hope this helps ...
Regards,
JP
-
You can create local and global temporary tables. Local temporary (#) tables are visible only in the current session; global temporary tables (##) are visible to all sessions.
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name.
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.
Temporary table can be explicitly dropped using DROP TABLE command.
-
Personally speaking I prefer Allen's method. Table variables UNLESS used for small lookup only type stuff I have found to be BAD.. I know we debate this all day long (in fact there are forums discussing this)
SQL server for everyone (including users and sp's) using #tablenamesameforeveryone has the ability to tie this #table belongs to this person/procedure. There is no possibility of cross contamination UNLESS you use ##tablenamesamehere. If that is the case then YES you would be in trouble
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Not sure, folks, but I think the original question was about having temp tables with the same name running in stored procedures that run at the same time. The answer, Sahana, is that temp tables that start with a single # sign are session sensitive and a call, via EXEC, seems to also be considered to be a "session". In fact, you shouldn't need to even drop the temp tables... they should drop "auto-magically".
There are those that say "table variables" should be used instead of temp tables... I agree if the table variables aren't going to carry a large amount of rows, say, over 50,000. Table variables try to live in memory and if you have the Standard Edition of SQL Server, you are limited to 2GB of memory no matter how much memory is available in the machine. If SQL runs out of memory because of an oversized table variable, the machine will start spooling to the swap file... all semblence of speed will vanish.
--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)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply