Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Is it "better" to specify the definition of a temporary table, or just define it on the fly? Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 8:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
WOW, lots of great feedback here, thank you everyone!

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1379343
Posted Tuesday, November 6, 2012 11:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:03 PM
Points: 259, Visits: 1,089
Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,
Post #1381673
Posted Tuesday, November 6, 2012 11:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
Completely false.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1381679
Posted Tuesday, November 6, 2012 1:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Lee Crain (11/6/2012)
Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,


Used to be partially true in SQL 7.5 and before. Schema locks on tempdb were needed in order to create temp tables back then. In any version of SQL Server from SQL 2000 or later, that is no longer true. But even in the older versions, the locks only lasted as long as it took to create the table, not for the duration of the inserts, if I remember correctly.

On the main subject, I use Select Into when the structure of the temp table is subject to change without notice, or when I'm trying to debug truncation issues and so on. I generally use Create...Insert...Select for temp tables where I want to control the structure of the temp table. In either case, I usually add a conditional Drop Table before the create step.

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL 
DROP TABLE #T;

CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
MyColumn1 INT NOT NULL);

INSERT INTO #T
(MyColumn1)
VALUES (1),
(2),
(5);

Like that.

Makes it so the script can be run repeatedly for debugging/testing/refactoring.

Breaking it up that way makes it more "self-documenting", it my opinion. I find it much easier to overlook an "INTO" in a Select statement, than to overlook a "CREATE TABLE" with some whitespace around it.

Plus habits from SQL 2000 and before, where mixing DDL and DML could cause performance issues - not as important any more, but it's a habit and I haven't bothered to change it.

So I tend towards explicitly creating temp tables.

One very useful application of Select Into, though, is when you get a truncation error from an ETL process that was working before. Use Select Into, pull the structure of the resulting table, compare it to your target table, and the column with a longer max_length will usually leap right out. If you name the columns the same as the target table, you can even do an Outer Join on the two sets of column definitions and find where the temp table has a bigger scope. Speeds up debugging that kind of thing marvelously when you have a dozen or more varchar columns, and all you get from the SQL engine is "string or binary data would be truncated", with no indication of what column/row you need to look at.

There are lots of other little shortcuts like that where Select Into can save a lot of time and work.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1381701
Posted Tuesday, November 6, 2012 2:30 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:03 PM
Points: 259, Visits: 1,089
Thanks, Gail, and thanks, G-Squared, especially for the details regarding debugging SSIS problems.

SSIS is not one of my strong points. I'm going to tuck that advice away for future reference.
Post #1381714
Posted Tuesday, November 6, 2012 2:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:23 PM
Points: 37,062, Visits: 31,626
GSquared (11/6/2012)
Lee Crain (11/6/2012)
Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,


Used to be partially true in SQL 7.5 and before.


I believe you meant 6.5 and before. SP1 of 6.5 fixed the problem and it's been a built in fix since 7.0. It still puts on some shared locks in TempDB which will make the SSMS GUI give a time out when you right click on tempdb for properties but it's so fast that it doesn't usually matter.

As of 2005, it still has a problem if the source is from a linked server (personal experience... no written proof). We did this once and it caused massive timeouts on the source server. You can try it but be aware of the possible consequences.

I do have a Microsoft link for the 6.5/SP1 thing at home. I'll try to remember to post it when I get hoem.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381715
Posted Tuesday, November 6, 2012 2:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:23 PM
Points: 37,062, Visits: 31,626
Lee Crain (11/6/2012)
Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,


It's a myth based on a previous truth from the RTM of 6.5 and earlier. It was fixed by 6.5/SP1.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381716
Posted Wednesday, November 7, 2012 6:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Jeff Moden (11/6/2012)
GSquared (11/6/2012)
Lee Crain (11/6/2012)
Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,


Used to be partially true in SQL 7.5 and before.


I believe you meant 6.5 and before. SP1 of 6.5 fixed the problem and it's been a built in fix since 7.0. It still puts on some shared locks in TempDB which will make the SSMS GUI give a time out when you right click on tempdb for properties but it's so fast that it doesn't usually matter.

As of 2005, it still has a problem if the source is from a linked server (personal experience... no written proof). We did this once and it caused massive timeouts on the source server. You can try it but be aware of the possible consequences.

I do have a Microsoft link for the 6.5/SP1 thing at home. I'll try to remember to post it when I get hoem.


Yeah, that's why I put the "if I remember correctly" bit in there. I haven't worked with anything older than SQL 2005 for a long time, so details of prior versions are all blurring together these days.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1381938
Posted Friday, November 9, 2012 3:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
worth reading

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1382924
Posted Friday, November 9, 2012 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:23 PM
Points: 37,062, Visits: 31,626
For those interested in MS documentation on the subject of TempDB locking IAW SELECT/INTO, please see the following URL. Note the line that says this has not been a problem since SQL Server 7.0.
http://support.microsoft.com/kb/153441/en-us


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383087
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse