[dba-SQLServer] SELECT INTO Versus CREATE #temp

Arthur/Asger,
  IME, when you do a SELECT * INTO #Temp you hold a lock on the TEMP db.  I
have not tested this against Sql Server 2005, but in Sql Server 2000 SP4
this was very evident on our legacy application.  if your Select * Into
takes several seconds to return you will be locking the TempDb for that
duration, in contrast, if you create the Temp Table first you do not lock up
the database while you are inserting data into the temp table.

On Fri, Jul 18, 2008 at 8:14 AM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> I just complete my test case and the difference on inserting 100 rows was
> less than one second. Hardly worth a rewrite.
>
> Arthur
>
> On Fri, Jul 18, 2008 at 11:05 AM, Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > I guess maybe I can only find out by writing a test case that inserts 1K
> or
> > 10k rows. None of the instances of such code impose any constraints, so
> it
> > may be 6 of one and 1/2 dozen of the other. Maybe they both resolve to
> > identical code beneath the covers; maybe not. I'll write my test case and
> > report back with the results.
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...