Solved

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

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check number of row prior to displaying gridview 10 36
How to share SSIS Package? 6 38
Get the latest status 8 32
Show Results for Latest DateTime in a View 27 25
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

832 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