[dba-SQLServer] SELECT INTO Versus CREATE #temp

Agree, only a global temporary table created with the ##-prefix should make
this behaviour possible. A temp table with the #-prefix is private to the
session creating the table.

Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Francisco
Tapia
Sendt: 18. juli 2008 23:17
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] SELECT INTO Versus CREATE #temp

Not at all, each execution of a sproc should be in it's own session. thus
after the 2nd create table, you should be able to view the Temp Tables in
the TempDB and see that you have #Asger000000000000000001 and
#Asger000000000000000002 (or some such number).  I'm not sure why User 1
would have User2's data unless you were createing ## temporary tables which
expand beyond the current users' session.


On Fri, Jul 18, 2008 at 2:12 PM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> I accept that, but you beg another interesting question. Suppose:
>
> User 1 invokes the sproc which says "CREATE TABLE #Asger" and then does
> some
> inserts.
> One second later,
> User 2 invokes the sproc which says "CREATE TABLE #Asger" and then does
> some
> inserts.
>
> Process #1 has not finished. What happens? Are we in danger here? We have
> occasionally noticed that User 1 ends up with data that belongs to user
#2?
> Could this be why?
>
> Time to put on the white coat and go into the lab and find out.
>
> Completely irrelevant comment: I'm so glad that I'm now devoted to these
> problems only, without having to worry about the Access or .NET front
ends.
> They belong to Shahid not me. I like this division a lot. (His desk is
only
> about 10 feet from mine, but we can concentrate on different problems. I
> think I won -- he is in DLL Hell currently. I've been there and done that
> and don't want to go back.
>
> It turns out after using Red Gate's tools that I can uncover no instances
> of
> "SELECT INTO" but there are a disturbing number of temp tables and now I
> have to figure out why. I've been reading about the performance hits of
> temp
> tables and ways to do it smarter, so now I know what to do this weekend --
> some comparisons.
>
> Thanks, Francisco and Asger, your input is always appreciated,
> Arthur
>
>
> On Fri, Jul 18, 2008 at 5:37 PM, Francisco Tapia <fhtapia at gmail.com>
> wrote:
>
> > 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.
> >
> >
> _______________________________________________
> 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...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com