Running SQL 2008R2 Standard with SP2, and db comparability level = 90 (SQL 2005).
Perhaps someone can shed light on this mystery I discovered. I have a table defined with a UNIQUE CONSTRAINT:
CREATE TABLE [dbo].[t1]
[col1] [int] IDENTITY(1, 1) NOT NULL
,[ProcessedDate] [datetime] NULL
,[col3] [int] NOT NULL
,CONSTRAINT [UQ_T1_Col1_ProcessedDate_Col3] UNIQUE NONCLUSTERED ([col1] ASC, [ProcessedDate] ASC, [col3] ASC)
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
You will notice that IGNORE_DUP_KEY = OFF, however, there are a number of dupes in the table. If I drop the constraint and re-apply, it fails with unique constraint violation.
Can anyone enlighten me as to how this seemingly contradictory condition came to be?