For instance, if I am executing the procedure the temp table name will be #xyz_my_id and if someone else executes the same procedure it creates temp table with the name #xyz_his_id. This way both of us can execute the same procedure without waiting for temp table to be dropped.
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.
This was first published in June 2006