I'm on SQL Server 2014 enterprise edition. I'm running into the following error when attempting to run a MERGE statement: "Cannot insert duplicate key row in object 'TABLENAME' with unique index 'INDEX_NAME'. The duplicate key value is (###, ###).". The strange thing is that the index that is referenced ("INDEX_NAME") is not a unique index, and it doesn't have 2 key columns as indicated in the error message. The definition of INDEX_NAME is as follows:
CREATE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME] ([FK_Column1] ASC)INCLUDE ([FK_Column2])
In addition, if I rebuild the index mentioned in the error message and then re-run the same exact MERGE statement, it succeeds. I'm lost as to what is going on here. The only thing that I can see that is a little different with this table is that the PK column is set up as a unique index, and the clustered index is on a different column (this design is out of my control, so its not an option to change it and see if it resolves the issue).
Any ideas on what is happening here?