EXPERT RESPONSE
If you create a private temporary table in your stored procedure many people should be able to execute the procedure simultaneously. By private temp table I mean a table with a single # such as #SalesTable. If you look in tempdb you will see the table actually created contains the name #SalesTable
with '_______________SOMEUNIQUEID' appended to it.
This naming convention actually allows one to create private temp tables with the same exact names as your neighbors. While we reference them as #SalesTable internally they are stored with the unique identifier value appended to it. If you create a global temporary table these are actual tables created 'as is' in the tempdb database with a double pound ## naming convention such as ##SalesTable.
With global temp tables you may experience locking and blocking. If you absolutely must use global temp tables you can consider creating the table using a guide as the table name (with the –'s removed of course because they aren't allowed in the table name).
In fact, I've created unique tables using the newid() function to generate a uniqueidentifier value.
|
