[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-11-28
10
Medium Priority
?
1,987 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 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 600 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 35

Author Comment

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

Assisted Solution

by:imitchie
imitchie earned 600 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 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 600 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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