Partially-Unique Check Constraints

I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is either null or defaulted to "00000000". However, going forward, we're wanting it to be set up that no duplicate values are allowed (lets say 1,000 records are null, 500 are "00000000", and ~10,000 are actual unique values we want).

Looking into it, since I have multiple records which are set to "00000000" and multiple which are null, I understand that I can't use the Unique constraint. Is there a way that I can create a Check constraint that checks if the value being inserted/updated is unique if not all 0's or null? Or if it's not workable via a check constraint, my other thought was maybe use an insert/update trigger to somehow validate the data?

You can use a filtered unique index for this:

CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null 
    ON dbo.oldAccessTable (LinkedID)  
    WHERE LinkedID <> '00000000' ;

The condition (LinkedID <> '00000000') takes care of both requirements, i.e. rows with '00000000' or NULL are ignored and not stored in the index, so they are not checked for uniqueness.

StackExchange Database Administrators Q#189675, answer score: 11