I have a job that runs a stored procedure, that seems to intermittently fail with a duplicate key error when creating a unique clustered index on a temp table. How is it possible for me to get this error based on the code below.
It seems to happen when the source table is being updated. Transaction Isolation Level is set to read uncommitted, and there ARE some triggers on "SomeTableWithPrimaryKey". It doesn't happen every time, just three times in the last 100 runs. There are about 3 million rows in the source table "SomeTableWithPrimaryKey", and about 500k of them are being selected after the filters are applied.
Is this a SQL Server Bug? Some race condition?
This statement is based on a true query, the names have been changed to protect the innocent...and my job. :)
--on SQL Server 12.0.5589 SELECT p.PrimaryKeyColumn, p.SomeOtherColumn INTO #Temp FROM dbo.SomeTableWithPrimaryKey p WHERE p.SomeCondition = 1 AND (p.SomeCondition2 = 2 OR EXISTS( SELECT 1 FROM dbo.SomeTable st INNER JOIN dbo.SomeOtherTable sot ON st.Column1 = sot.Column1 WHERE st.SomeCondition1 = 1 AND st.PrimaryKeyColumn = p.PrimaryKeyColumn ) ) ; --productid index CREATE UNIQUE CLUSTERED INDEX CX_Temp ON #Temp(PrimaryKeyColumn) ;
^this is where we get the error:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#Temp______...000000D5A436' and the index name 'CX_Temp'. The duplicate key value is (123456789).
[SQLSTATE 23000] (Error 1505) The statement has been terminated.
[SQLSTATE 01000] (Error 3621). The step failed.