Francisco Tapia
fhtapia at gmail.com
Fri Jul 18 16:17:12 CDT 2008
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...