-1

I have a table with many records. Column X is nullable and a lot of records have NULL in this column. Can I create a UNIQUE non-clustered index on this column? Aren't those null values violating the unique constraint?

Improve this question
4

1 Answer 1

Reset to default
9

If you're on SQL Server 2008 or newer - you can set up a filtered, unique index to exclude / ignore all the NULL values.

CREATE UNIQUE NONCLUSTERED INDEX uixYourIndexName
ON dbo.YourTableName(YourColumnName)
WHERE YourColumnName IS NOT NULL

That would essentially "filter out" all NULL values - not including them into the index at all. Any query that uses the same WHERE clause can use this filtered index to find those rows.

Without the filtered index, you could not create a UNIQUE INDEX on your column since with a UNIQUE index, you're only allowed to have a single row that has NULL in that column.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Does this exclude rows with a null value, or exclude null values when creating the index? IE with rows 1, 2, null and 1, 2, null, does this violate the constraint for third column IS NOT NULL or not?
@CaptainPrinny: the WHERE clause just skips any rows that match the condition - so here, any row with NULL in the YourColumnName will not be added to the index and cannot be found by using that index

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.