Solved

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

Posted on 2007-11-28
10
1,943 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:David Todd
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
 
LVL 35

Author Comment

by:David Todd
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
>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
 
LVL 35

Author Comment

by:David Todd
Comment Utility
Thanks AngelIII.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 150 total points
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 35

Author Comment

by:David Todd
Comment Utility
Thanks imitchie.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
Comment Utility
>>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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
>> 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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 150 total points
Comment Utility
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
 
LVL 35

Author Closing Comment

by:David Todd
Comment Utility
Hi Folks,

Thanks for your input.

Cheers
  David
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now