select ... into #TempTable vs Create #TempTable & insert #TempTable select

Hi,

Question:
Does select field1, field2 into #SomeTempTable from SomeOtherTable hold a schema lock for the entire duration of the operation, or just while the temp table is being created, and does this result in blocking other temp table creation.

Or is it simply a preferred practice to explicitly create and then populate a temp table. That is, the resulting T-SQL is easier to read and maintain than select * into #TempTable?

Background:
I came across this comment recently
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22984077.html

When I referred to this, imitchie suggested that the background reason that was trotted out was out-of-date. (see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22985646.html#a20364908)

So, wanting to learn, I would like to discuss this, and get more input - hopefully with a few authoritive references, rather than one or two opposing opinions.

If this advice applied at all, to what versions of SQL? (I still have some SQL 7.0 around here), and when (if this is considered a bug) was this fixed? Was it fixed in a service pack?

Cheers
  David
LVL 35
David ToddSenior DBAAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>i know for a fact that it was a problem in sQL70 due to lack of design foresight, and it's sybase background. <<
Just a minor correction, you could argue that SQL Server 6.x still had some Sybase in it, but SQL Server 7 was a total re-write.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
unless you are "forced" for any reason to use #temp or ##temp tables, you should think about using @temp table variable method.
now, personally, I avoid the select into completely...
0
 
David ToddSenior DBAAuthor Commented:
Hi angelIII,

I read somewhere that bigger results sets, say in overnight batch updates and loads, are better with temp tables instead of table vairables.

Cheers
  David

PS Half our systems are still on SQL 7.0, the other half are on SQL 2000. I did have a Christmas Break project pencilled in to update to SQL 2005, but our industry has recently taken a hit, and so finances being tight, the project got canned ...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>I read somewhere that bigger results sets, say in overnight batch updates and loads, are better with temp tables instead of table vairables.

if they are later reused, and some indexes are created to it to ensure later queries on those tables will go fast, yes, that is a perfectly good reason to do so.

in sql 7, also, you cannot use table variables, so you are limited there too.

0
 
David ToddSenior DBAAuthor Commented:
Thanks AngelIII.
0
 
imitchieConnect With a Mentor Commented:
i know for a fact that it was a problem in sQL70 due to lack of design foresight, and it's sybase background. by sql2000, they had restructured the locks to be much more refined that removes this problem. i'll dig up some references later
0
 
David ToddSenior DBAAuthor Commented:
Thanks imitchie.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> Does select field1, field2 into #SomeTempTable from SomeOtherTable hold a schema lock for the entire duration of the operation <<

Yes.  That's why it's better if possible to do this:

SELECT TOP 1 field1, field2
INTO #SomeTempTable
FROM SomeOtherTable
WHERE 1 = 0

This doesn't insert any rows, just creates the table.  Then you can use a normal INSERT to populate the table w/o holding the schema locks.


>> does this result in blocking other temp table creation <<

It could, depending on what else is going on.  Also, if you have not created multiple data files in the temp db, you can see contention on the GAM and other internal allocation pages for SQL whenever you are loading a table.


>> when (if this is considered a bug) was this fixed? Was it fixed in a service pack? <<

Don't think it's technically a bug, since atomicity would require the *entire* statement -- including the table create -- commit or rollback.  Not sure if SQL's internal code was changed to free the schema locks after table creation in 2K and/or 2K5; pretty sure it's not changed in 7.0.


>> I read somewhere that bigger results sets, say in overnight batch updates and loads, are better with temp tables instead of table variables. <<

Agree.  Especially if you do lots of querying against the resulting tables.
0
 
imitchieConnect With a Mentor Commented:
SQL 7 locked tempdb..sysobjects at the table level on some types of Select Into. SQL 2000 doesn't

http://www.sqlservercentral.com/articles/Performance+Tuning/temptables/148/
Problem in 6.5, mostly fixed in 7.0

http://support.microsoft.com/kb/162361
(this link also contains detailed info on how to check locks. for those inclined, verify SQL 7.0 locks obtained for SELECT INTO vs those for 2000/2005. you'll find that I am right)
VI. Blocking Caused by SQL Server 6.5 Atomic SELECT INTO Behavior

By definition, SQL Server treats each statement as a separate transaction. Beginning with SQL Server version 6.5, SELECT INTO was made consistent with this standard by including the table creation and data insert phases in a single atomic operation. A side effect of this is that locks on system catalog tables are maintained for the duration of a SELECT INTO statement. This is more frequently seen in tempdb, because applications often do SELECT INTO temporary tables. Blocking caused by this action can be identified by examining the locks held by the blocking SPID. The atomic SELECT INTO behavior can be disabled with trace flag 5302. For more information, see the following article in the Microsoft Knowledge Base:
153441 (http://support.microsoft.com/kb/153441/EN-US/) FIX: SELECT INTO Locking Behavior
0
 
David ToddSenior DBAAuthor Commented:
Hi Folks,

Thanks for your input.

Cheers
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.