duplicate key error creating unique index on temp, SQL Server 12.0.5589 bug?

Question

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.

Answer 1

When using the READ UNCOMMITTED isolation level, it is possible for SQL Server to read the same data twice - causing you to get duplicate results, even if there is a unique constraint ensuring there are no duplicates of that data in the table.

Looking at some of the comments on the question:

Thanks, the general consensus seems to be the iso level, but keep in mind, i've probably used that same basic query above in hundreds of places, and have never seen this behavior before

This won't happen all the time, and can depend a lot on whether the source table is being updated, the physical operations SQL Server chooses to use to read the data, and the nature of the query being run.

I will change the iso level and watch it (for possibly a year) to see if we get the same error in the future

That's great, changing the isolation level is your best option for preventing this problem!

When you have time, there is some great information out there on the issues around using the READ UNCOMMITTED isolation level:

• Paul White's post (in a series about isolation levels): The Read Uncommitted Isolation Level
• A blog post from Brent Ozar with a very clear demo of how results can change under READ UNCOMMITTED: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.