Sql-Server Unique Constraint but with NON-unique data I've inherited this database which has a table defined as such :

CREATE TABLE home_tickets(
    id int IDENTITY(1,1) NOT NULL,
    identifier nvarchar(45) NOT NULL,
    start_date_time datetime NOT NULL,
    revised bit NOT NULL,
    agency_code nvarchar(100) NOT NULL,
    archived tinyint NOT NULL,

    CONSTRAINT PK__home_tic__3213E83F236943A5 PRIMARY KEY CLUSTERED (id ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY,

    CONSTRAINT UQ__home_tic__969168722645B050 UNIQUE NONCLUSTERED (identifier ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY


However when I select from the table given an identifier I am getting multiple rows returned.  

select * from home_tickets where identifier = 'TI54815780';


id      identifier  start_date_time         revised archived
415044  TI54815780  2013-10-24 00:00:00.000 0       0
652845  TI54815780  2014-10-24 00:00:00.000 0       0
171642  TI54815780  2012-10-24 00:00:00.000 0       0
945275  TI54815780  2015-10-24 00:00:00.000 1       0
1220327 TI54815780  2016-10-24 00:00:00.000 1       0


Why would this happen with a unique constraint on identifier ?
It seems the underlying constraint index is disabled.  You can verify this with:

SELECT is_disabled
FROM sys.indexes
WHERE
    object_id = OBJECT_ID(N'home_tickets')
    AND name = N'UQ__home_tic__969168722645B050';


After the bad values are remediated, rebuild the index to enable the constraint:

ALTER INDEX UQ__home_tic__969168722645B050 ON home_tickets REBUILD;