Unique Index on Nullable Columns in SQL Server

I came across a surprising error inserting data into a table which has a composite unique index, where one column is defined as nullable:

Cannot insert duplicate key row in object ‘dbo.MyTable’ with unique index ‘UK_MyTable’.

I say “surprising” because I always (or at least, at the day I declared the index) thought that NULL values were not part of an index, as is the case with Oracle databases (thus causing difficulties search an index for NULL values).

So while there are workarounds for earlier versions of SQL Server, such as indexing a VIEW WHERE columnX IS NOT NULL or indexing a computed columns, 2008 introduced filtered indexes, and you can now write

    [NotNullableCol] ASC,
    [NullableCol] ASC

Bonus: To find all nullable columns which are part of an index, use this statement, and check whether the filter_definition column is set for each index:

select o.name, c.name, i.name, i.filter_definition, * 
from sys.columns c
inner join sys.objects o 
  on o.object_id = c.object_id
inner join sys.index_columns ic 
  on ic.object_id = o.object_id and ic.column_id = c.column_id
inner join sys.indexes i 
  on ic.index_id = i.index_id and i.object_id = o.object_id
where is_nullable = 1
and is_ms_shipped = 0
order by 1, 2, 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: