October 21, 2011 at 10:11 pm
Do you use a CreateTempTable function to create temp tables in your T/SQL?
Basically when you create a temp table as part of a T/SQL script you have to first check for the existence of the table in tempdb using a LIKE clause. If the table exists in tempdb then you need to drop the table. Then you need to recreate the temp table.
I don't write much SQL these days but I have to think that a lot of you use a special reusable database function like "CreateTempTable" to encapsulate this process.
If you have a tried and true function that you use for this then will you please post it to this thread?
October 22, 2011 at 1:20 am
No, I have not. First of all, I rarely create global temp tables. I use local temp tables instead, which is lokal to the session. I drop there tables when I'm done with them. Second, functions cannot be side affecting, so it would have to be a stored procedure.
October 22, 2011 at 7:11 am
sqlguy-736318 (10/21/2011)
Basically when you create a temp table as part of a T/SQL script you have to first check for the existence of the table in tempdb using a LIKE clause. If the table exists in tempdb then you need to drop the table. Then you need to recreate the temp table.
I never write code that way. Create only local temp tables, ensure that they are correctly dropped at the end of the procedure (though SQL will do it for you) and there's no need to check for existence.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 8:04 am
sqlguy-736318 (10/21/2011)
Do you use a CreateTempTable function to create temp tables in your T/SQL?Basically when you create a temp table as part of a T/SQL script you have to first check for the existence of the table in tempdb using a LIKE clause. If the table exists in tempdb then you need to drop the table. Then you need to recreate the temp table.
I don't write much SQL these days but I have to think that a lot of you use a special reusable database function like "CreateTempTable" to encapsulate this process.
If you have a tried and true function that you use for this then will you please post it to this thread?
sqlguy-736318 (10/21/2011)
Do you use a CreateTempTable function to create temp tables in your T/SQL?Basically when you create a temp table as part of a T/SQL script you have to first check for the existence of the table in tempdb using a LIKE clause. If the table exists in tempdb then you need to drop the table. Then you need to recreate the temp table.
If you attempt to create the temp table using the same connection, yes your statement above is correct. However if you are using two or more DIFFERENT connections there is no need to drop the temp table.
For example create a temp table, using 2 DIFFERENT connections but executing the same code for example:
CREATE TABLE #temp(id INT )
and then check using
SELECT name
FROM sys.tables WHERE name LIKE '%#temp%'
Result: (Name shortened for showing in this posting)
name
#temp___________________________________________000000000017
#temp--_________________________________________00000000000A
FROM:
It is possible to create and use local temp table with the same name simultaneously in two different connections. In order to allow this behavior SQL Server suffixes name of the local temp table with incremental hex digit,
October 22, 2011 at 9:30 am
I agree with what most of the folks have said about Temp Tables on this thread. I do, however, do a conditional drop of Temp Tables at the beginning of my procs not because they're needed, but because ...
1. I had those conditional drops at the top of my development code while I was developing the proc to make my life easy.
2. I left them there to make life easier for the next person who may have to modify the code.
3. It costs virtually nothing to do so and adds "warm fuzzies" if someone is operating in a "weird" environment.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2011 at 11:24 am
Thanks for your replies everybody. Sounds like table variables should be used instead of local temp tables. Table variables seem to have a smaller footprint and local temp tables seem like the old way of doing things.
One question about temp tables though - does the additional info at the end of the temp table name in tempdb represent the session ID of the machine that created the temp table? Is that ID uniquely tied to the machine? For example, if I restarted my machine and tried to recreate the same temp table the next day then would SQL server say that the table already existed? Or is the information at the end just a random ID generated by SS for internal indexing purposes?
October 22, 2011 at 11:32 am
NNNNNNNNNNNNNOOOOOOOOOOOOOOOOOO
The correct way is simply this
IF OBJECT_ID('temdb..#tblname', 'U') > 0
DROP TABLE #tblname
The short answer is that you use a @table variable only when you can't do what you need with a #temp table.
The main reason is that you'll get the best chance at getting the correct estimates in the plans, contrary to @table which will very often not only hurt, but KILL performance.
October 22, 2011 at 11:42 am
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.
***The first step is always the hardest *******
October 22, 2011 at 11:47 am
Thanks Ninja - I assumed that table variables would always have better performance than temp tables but based on your emphatic NOOOO it sounds like you have a lot of experience with this....
October 22, 2011 at 11:48 am
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.
Same thing with temp table.
October 22, 2011 at 11:51 am
sqlguy-736318 (10/22/2011)
Thanks Ninja - I assumed that table variables would always have better performance than temp tables but based on your emphatic NOOOO it sounds like you have a lot of experience with this....
My best exemple was in a SSRS report. A rather big one but nothing huge.
The whole DB at the time was about 20 GB which is roughly 2.6 M pages fully loaded.
The report was touching many of the big tables... maybe a total of 500K pages at the absolute most.
When I looked at the final execution in profiler, the page read count was a stagering 50 M pages. That's the whole db 20 times over.
Switch 2 @tables to #tables and the total went down to 2M. Which is reasonable for what that report was doing.
And yes 50 M reads was the fully optimized version (index wise, every table access had a covering index if the normal keys were no enough).
Bad estimates, especially at volume will kill you everytime.
October 22, 2011 at 11:55 am
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.
Thanks accidental - table variables do seem to be the best for adhoc queries as opposed to online oltp queries because the SQL seems cleaner and more centralized.
October 22, 2011 at 11:58 am
Ninja's_RGR'us (10/22/2011)
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.Same thing with temp table.
Ninja - not exactly - don't you have to drop the temp table at the beginning?
October 22, 2011 at 11:59 am
sqlguy-736318 (10/22/2011)
Ninja's_RGR'us (10/22/2011)
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.Same thing with temp table.
Ninja - not exactly - don't you have to drop the temp table at the beginning?
How do you drop something that does not exists?
October 22, 2011 at 12:04 pm
sqlguy-736318 (10/22/2011)
AccidentalDBA:) (10/22/2011)
Depending on what you are doing i like to user variable temp tables, there is no need to drop them at the end because once the script as completed execution the variable temp table no longer exists.Thanks accidental - table variables do seem to be the best for adhoc queries as opposed to online oltp queries because the SQL seems cleaner and more centralized.
Hmm, do you know that #tables are local to the connection? I'm not sure you understand all the basics of those objects.
What do you mean by cleaner (aside from the non-required drop)?
What do you mean by more centralized? The only way that can happen is if you put that in a function... which then means it's not really a local variable anymore.
I can see good arguments on this angle but I'm not sure you really understand t-sql. Is you background comming from OO programming rather than DBA? There's nothing wrong either way but it would help us help you better if we knew.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply