Can we create Unique clustered index on columns having Null values

Question

Hello,

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> "

Answer 1

Hi,

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)

Answer 2

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.