We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK constraint like so:
CREATE OR ALTER PROCEDURE dbo.StoredProc AS BEGIN CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id)) ... END
Querying tempdb.sys.objects reveals a couple of hundred constraints of the form PK__#TempTab__XYZ. Many were created hours ago and haven’t been modified since. There were very few active sessions when I looked at tempdb.sys.objects, so it is hard to believe there are that many temp tables currently in use. We do have many similarly named temp tables created in a lot of stored procedures.
I think temp table caching is responsible here. It appears that these PK constraints stick around in tempdb until their associated cached plans are removed. Testing has shown that causing temp table caching to be disabled (creating stats, adding named constraints, etc.) causes the PK constraint to get removed from tempdb.sys.objects when the temp table goes out of scope. Triggering recompiles and flushing the proc cache also clear out those PK constraints from tempdb.sys.objects.
I am aware that system-named constraints are not guaranteed to be unique as described in this post Can SQL Server create collisions in system generated constraint names?.
My questions are:
- Am I wrong to assume that temp table caching increases the chance of a name conflict for system-named constraints on temp tables?
- Could plan cache bloat be responsible for the large number of PK__#TempTab__XYZ constraints that I am seeing in tempdb.sys.objects, thus increasing the chance of two constraints having the same name? I'm trying to understand why we have seen this rare error this often.
- If we can't ensure that system-named constraints on temp tables will never encounter naming collisions, what can we do to reduce the chance of these collisions in general?