[dba-SQLServer] SELECT INTO Versus CREATE #temp

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.
>
>