March 19, 2014 at 11:58 am
Hi everyone,
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]
)
ON [PRIMARY]
GO
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?
Thanks,
SQLNYC
March 19, 2014 at 12:01 pm
my apologies, that should have been without the IDENTITY:
CREATE TABLE [dbo].[t1]
(
[col1] [int] 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]
)
ON [PRIMARY]
GO
March 19, 2014 at 12:31 pm
It appears that the index was disabled.
March 19, 2014 at 12:33 pm
That'll do it. Glad you figured it out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply