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.
Dig Deeper on SQL Server Stored Procedures
Related Q&A from Joe Toscano
Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed. Continue Reading
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ... Continue Reading
Getting an error when importing data from an Excel spreadsheet to SSIS in SQL Server 2005? Try lining up data types in Excel and SSIS so they match. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.