Creating or dropping temporary tables and their indexes can cause lock contention on the system tables in temporary databases. When users create temporary tables, information about the tables must be stored in system tables such as sysobjects, syscolumns, and sysindexes in the temporary database. If multiple user processes are creating and dropping tables in a temporary database, heavy contention can occur on the system tables. Worktables created internally do not store information in system tables.
If contention for temporary database system tables is a problem with applications that must repeatedly create and drop the same set of temporary tables, try creating the tables at the start of the application, then use insert...select to populate them, and truncate table to remove all the data rows. Although insert...select requires logging and is slower than select into, it can provide a solution to the locking problem.
See “Creating user temporary databases” for more information about how to avoid blocking due to locking of system tables’s multiple user temporary databases.
