Hi Stephen,

The problem with creating and dropping a "normal" table in a stored procedure is that two users might try to run the same procedure at the same time and step all over each other, trying to access the "real" table.

A local temporary table (prefixed by # as opposed to ##), on the other hand, is local to a connection, so two users won't interfere with each other provided they are using 2 different connections to the database.

What happens with local temporary tables is that behind the scenes, a "real" table is created in tempdb with a lot of underscores and some hex digits added on to the the name you see. If my friend Sam and I both run a stored procedure that creates a temp table #Foo, what happens is that Sam creates a table in tempdb that will be called something like #Foo________________________________________________________________________________________________________________000000017D5D

while I will create, for example, one called #Foo________________________________________________________________________________________________________________000000029A4F

(if you scroll all the way to the right, you'll see that the last several digits in the name are different)

Regards,

SteveR