CREATE UNIQUE INDEX terminated because a duplicate key was found

Creating a unique index on a column in a table can fail if duplicate values are found. When attempting to make a column unique on a table that has been in use for a while, the CREATE UNIQUE INDEX statement may terminate with an error. The error message specifies that the duplicate key value was found for the object and index names.

This issue can be resolved by creating a filtered unique index that excludes null values from being considered duplicates. The SET QUOTED_IDENTIFIER ON statement is necessary, as it is often overlooked and is default in SQL Server Management Studio but not in other environments like sqlcmd. By using a filtered index, multiple rows can have no email value, but any rows with an email must be unique.

To identify duplicate values, a query can be run to count the occurrences of each email and select the row to keep based on its AccountId.

When deleting duplicate rows, it is important to consider any foreign key constraints referencing the table. The sys.foreign_keys view can be used to identify tables with rows pointing to the duplicate accounts. Deleting the duplicate rows while updating the foreign key references can be done using a CTE to rank the rows by a chosen criteria, such as AccountId.

The DELETE statement should be run as a SELECT first to verify the desired outcome before actually removing the rows. Finally, adding the unique index after resolving the duplicates can prevent further duplicate entries in the future. Tools like WoodFireERD can help visualize the dependencies and generate scripts for the necessary cleanup and modifications.