Is it "better" to specify the definition of a temporary table, or just define it on the fly?

Question

Is it "better" to specify the definition of a temporary table, or just define it on the fly?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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.

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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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.

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)

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

worth reading 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

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.

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)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Evil Kraig F (10/30/2012)
...
CREATE TABLE #Tmp CREATE INDEX idx1 INSERT INTO #tmp SELECT
runs about 1.5x - 2x faster (depending on the index) as
SELECT INTO #tmp CREATE INDEX idx1
...

Could you please post some proves of the above?

I think if it is a case, it would be something very specific to what you are doing, but even that is most likely can be changed to make SELECT INTO with INDEX faster than CREATE TABLE...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]