temporary table creation
-
As regards performance, is there any difference in creating temp tables using Select Into vs. Create Table?
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Select Into can create locks on the source tables which can block other processes. They have done better in recent times but it has always been better to Create and do Insert Into instead of Select Into to avoid this potential. But sometimes that is not an option.
-
Besides the locking issues already mentioned, I've found that I'll sometimes get odd table structures by using SELECT INTO - especially where numerics are concerned. It's been awhile since I've had this problem because I don't do it that way anymore, but one of the issues was that SELECT INTO would sometimes create datatypes of NUMERIC(18,38) which is something apparently that SQL will let itself do, but not YOU. As I recall, the precision exceeded what the maximum precision level is for that server - or something like that.
I was always having to go back and fix the procs because they would fail later when I tried to insert records from those "select into" temp tables. So, if you know the expected structure, just code the CREATE TABLE and use INSERT INTO (or use a quick n dirty SELECT INTO JUNK and use cut n paste to get the structure the first time).
-
I asked about the blocking in another forum earlier today and was told that was a 6.5 version problem and has since been fixed.
Another issue to consider is the affect on TempDB. If you create and destroy a real table, that affects the database you are running within. If you make a #TempTable that is created in TempDB and can push it hard if you have any space issues and many processes are running...
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply