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
CREATE UNIQUE NONCLUSTERED INDEX [UK_NotNull_Null] ON [dbo].[MyTable] ( [NotNullableCol] ASC, [NullableCol] ASC ) WHERE [NullableCol] IS NOT NULL GO
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