Thursday, August 30, 2012 1:48 PM
A quick question, Can we create Unique Clustered Index(UCI) on table having some of the column values are Null.
Actually we had a Job process in which unfortunately some null values came inside table 'A' for the column 'X'. And when trying to create UCI on table 'A' for the same column'B' its failed givving below error.
"The Unique clustered Index statement terminated because a duplicate key was found
for object name dbo.A, The duplicate key value is <NULL> "
Thursday, August 30, 2012 2:03 PM
Starting with 2008, you can create filtered unique index as below
Create unique index ix_a_x on a(x) where x is not null
- Chintak (My Blog)
Thursday, August 30, 2012 2:12 PMModerator
Chintak has a good point about the filtered indices. If you are not going to filter, you can create unique index that includes a column that is nullable. However, the values -- including nulls -- must be unique. So if you try to build a unique index of any kind -- whether clustered or not -- on a single column, that column must contain no more than one null value or the index create will fail. For example:
--drop table dbo.test create table dbo.test( aColumn int ); insert into dbo.test select 1 union all select 2 union all select null; create unique clustered index what on dbo.test (aColumn); /* -------- Output: -------- Command(s) completed successfully. */ drop index dbo.test.what; insert into dbo.test values(null); select * from dbo.test; /* -------- Output: -------- aColumn ----------- NULL 1 2 NULL NULL (5 row(s) affected) */ create unique clustered index what on dbo.test(aColumn); /* -------- Output: -------- Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.test' and the index name 'what'. The duplicate key value is (<NULL>). The statement has been terminated. */
* To get these results, the commands must be run one command at a time.