SELECT INTO vs CREATE/INSERT/SELECT

[Peter Ty]

I learnt that SELECT INTO can lock up system tables and if used as a long
running transaction can be very bad for the system as a whole, but the speed
gain of this non-logged operation is really irresistable, somebody once said
that it should be avoided like a plague, but I think it is ok for use in a
nightly run DTS job, especially in a datawarehousing database. Any comment
on this? I like to know how many would prefer this approach in coding
datawarehousing projects.

Thanks
Peter Ty

[Roy Harvey]

Peter,

>I learnt that SELECT INTO can lock up system tables and if used as a long

>running transaction can be very bad for the system as a whole.....

I do not believe this is the case for 7.0, and I know that for earlier
releases it was only true for release 6.5 and even for 6.5 it was
avoidable for #temp tables at least. Service Pack 1 (SP1) for release
6.5 introduced a trace flag to fix this; we implement it by adding the
command line parameter -T5302 to SQL Server. See Knowledge Base (KB)
article Q153441 for details.

Roy

[Tony Rogerson]

In 6.5 select into will lock system tables namely sysobjects, sysindexes and
syscolumns - trace flag 5302 was introduced in sp1 to get round the problem.

In 7.0 no such problem exists.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk (UK User group, code library, FAQ etc..)

Peter Ty <pet...@hotmail.com> wrote in message
news:O9oCXo$j$GA.282@cppssbbsa04...

> I learnt that SELECT INTO can lock up system tables and if used as a long

[William Talada]

I said it should be avoided like the plague in 6.5 and was unsure about 7.0.
Another reason to avoid it is because it is non-logged which invalidates
your transaction logs! If this is being done right before a full backup
then it is not a problem.

Tony Rogerson wrote in message ...