Solved

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

Posted on 2007-11-28
10
1,965 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 20367910
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
ID: 20367955
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 20368000
>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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 35

Author Comment

by:David Todd
ID: 20368062
Thanks AngelIII.
0
 
LVL 25

Assisted Solution

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

Author Comment

by:David Todd
ID: 20368123
Thanks imitchie.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 20368177
>>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:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 20368376
>> 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
ID: 20368580
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
ID: 31411501
Hi Folks,

Thanks for your input.

Cheers
  David
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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