The filtered unique index is a brilliant idea but it has a minor disadvantage - no matter if you use the
WHERE identity_column > <current value> condition or the
WHERE identity_column NOT IN (<list of ids for duplicate values here>).
With the first approach , you will still be able to insert duplicate data in the future, duplicates of existing (now) data. For example, if you have (even only one) row now with
CompanyName = 'Software Inc.', the index will not forbid the insertion of one more row with same company name. It will only forbid it if you try twice.
With the second approach there is an improvement, the above will not work (which is good.) However, you will still be able to insert more duplicates or existing duplicates. For example, if you have (two or more) rows now with
CompanyName = 'DoubleData Co.', the index will not forbid the insertion of one more row with same company name. It will only forbid it if you try twice.
(Update) This can be corrected if for every duplicate name, you keep out of the exclusion list one id. If, like the above example, there are 4 rows with duplicate
CompanyName = DoubleData Co. and IDs
4,6,8,9, the exclusion list should have only 3 of these IDs.
With the second approach another disadvantage is the cumbersome condition (how much cumbersome depends on how many duplicates there are in the first place), since SQL-Server seems to not support the
NOT IN operator in the
WHERE part of filtered indexes. See SQL-Fiddle. Instead of
WHERE (CompanyID NOT IN (3,7,4,6,8,9)), you'll have to have something like
WHERE (CompanyID <> 3 AND CompanyID <> 7 AND CompanyID <> 4 AND CompanyID <> 6 AND CompanyID <> 8 AND CompanyID <> 9) I'm not sure if there are efficiency implications with such a condition, if you have hundreds of duplicate names.
Another solution (similar to @Alex Kuznetsov's) is to add another column, populate it with rank numbers and add a unique index including this column:
ALTER TABLE Company
ADD Rn TINYINT DEFAULT 1;
SET Rn = Rnk
Rnk = ROW_NUMBER() OVER (PARTITION BY CompanyName
ORDER BY CompanyID)
) x ;
CREATE UNIQUE INDEX CompanyName_UQ
ON Company (CompanyName, Rn) ;
Then, inserting a row with duplicate name will fail because of the
DEFAULT 1 property and the unique index. This is still not 100% foolproof (while Alex's is). Duplicates will still slip in if the
Rn is explicitly set in the
INSERT statement or if the
Rn values are maliciously updated.