I have a table which currently has duplicate values in a column.

I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.

Can I create a UNIQUE that doesn't check for existing compliance?

I have tried using NOCHECK but was unsuccessful.

In this case I have a table which ties licensing information to "CompanyName"

EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.

This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.

  • Are you allowed to change the table (add one more column)? Jun 4, 2013 at 20:17
  • @ypercube unfortunately not.
    – Matthew
    Jun 4, 2013 at 20:46

5 Answers 5


The answer is "yes". You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;

This creates a unique index, only on new rows, rather than on the old rows. This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);
  • 2
    Whether or not that's good would depend upon whether "old" existing items should prevent the creation of new items with the same value.
    – supercat
    Jun 4, 2013 at 18:34
  • 1
    @supercat . . . I gave an alternative formulation for building the index on everything except existing duplicate values. Jun 4, 2013 at 18:35
  • 3
    For the latter to work, one would have to ensure that one omitted from the list one id for each distinct key value that had duplicates, and would also have to ensure that if the item which was deliberately omitted from the list got removed from the table, an item with an equal key would get removed from the list.
    – supercat
    Jun 4, 2013 at 18:44
  • @supercat . . . I agree. Keeping the index consistent for updates and deletes is all the more challenging because you can't re-create the index in a trigger. In any case, I had the impression from the OP that the data -- or at least the duplicates -- are not changing often, if at all. Jun 4, 2013 at 18:53
  • Why not exclude a list of values instead of a list of IDs? Then you don't have to exclude one ID per duplicated value from the list of excluded IDs Nov 29, 2017 at 19:14

Yes you can do that.

Here is a table with duplicates:

           IDENTITY ,
  ) ;

INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' ),
        ( 'Luke Skywalker' ),
        ( 'Luke Skywalker' ),
        ( 'Harry Potter' ) ;

Let us ignore existing ones, and ensure that no new duplicates can be added:

-- Add a new column to mark grandfathered duplicates.
ALTER TABLE dbo.Party ADD IgnoreThisDuplicate INT NULL ;

-- The *first* instance will be left NULL.
-- *Secondary* instances will be set to their ID (a unique value).
UPDATE  dbo.Party
SET     IgnoreThisDuplicate = ID
FROM    dbo.Party AS my
                 FROM   dbo.Party AS other
                 WHERE  other.Name = my.Name
                        AND other.ID < my.ID ) ;

-- This constraint is not strictly necessary.
-- It prevents granting further exemptions beyond the ones we made above.
ADD CONSTRAINT CHK_Party_NoNewExemptions 
CHECK(IgnoreThisDuplicate IS NULL);

SELECT * FROM dbo.Party;

-- **THIS** is our pseudo-unique constraint.
-- It works because the grandfathered duplicates have a unique value (== their ID).
-- Non-grandfathered records just have NULL, which is not unique.
CREATE UNIQUE INDEX UNQ_Party_UniqueNewNames ON dbo.Party(Name, IgnoreThisDuplicate);

Let us test this solution:

-- cannot add a name that exists
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- cannot add a name that exists and has an ignored duplicate
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Luke Skywalker' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- can add a new name 
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

-- but only once
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.
  • 4
    Except he can't add a column to the table. Jun 4, 2013 at 21:09
  • 5
    I like how this answer turns how NULL values are treated in non-standard way in unique constraint into something useful. Cunning trick. Jun 4, 2013 at 21:33
  • @ypercubeᵀᴹ, could you explain what is non-standard about the NULL handling in unique constraints? How is it different from what you would have expected? Thanks!
    – Noach
    Nov 29, 2017 at 16:46
  • 2
    @Noach in SQL Server, a UNIQUE constraint in a nullable column ensures that there is at most a single NULL value. The SQL standard (and almost all other SQL DBMSs) says that it should allow any number of NULL values (i.e. the constraint should ignore null values). Nov 29, 2017 at 18:47
  • is it bad if we ignore the old data forever without intention to fix it and remove the unique condition?
    – buncis
    Apr 25, 2022 at 18:49

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:


SET Rn = Rnk
      Rnk = ROW_NUMBER() OVER (PARTITION BY CompanyName 
                               ORDER BY CompanyID)
    FROM Company 
  ) x ;

  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.



Another alternative is to write a scalar function that checks if a value already exists in the table and then call that function from a check constraint.

This will do horrible things to performance.


I'm searching for the same - create an untrusted unique index so existing bad data is ignored, but new records can't be duplicates of anything that already exists.

While reading this thread, it comes to me that a better solution is to write a trigger which will check [inserted] against the parent table for duplicates, and if any duplicates exist between those tables, ROLLBACK TRAN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.