SQL SERVER – Unique Indexes on Nullable Columns

When managing databases, we sometimes encounter interesting challenges that require a bit of creative problem-solving. One such situation is creating a unique index on a nullable column. If you’re working with a SQL Server, this might be tricky as the server allows only one null value in a column if a unique constraint is applied. Let us learn about Unique Indexes on Nullable Columns.

The Problem – Nullable Columns

Consider a situation where you have a table with many records. You have a column, let’s call it Column X, that is nullable, and many records have NULL in this column. The question now is – can you create a unique non-clustered index on this column? Will these null values violate the unique constraint? Yes, they do, and they give an error.

The Solution

If you are using SQL Server 2008 or newer, you can use a filtered unique index to exclude or ignore all the null values. Here is how you can do it:

CREATE UNIQUE NONCLUSTERED INDEX UniIndex
ON dbo.TableName(YourColumnName)
WHERE ColumnName IS NOT NULL

This command will create a unique non-clustered index called UniIndex on the table TableName for ColumnName. The WHERE clause is the key here. It filters out all null values, not including them in the index. Hence, 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 with NULL in that column.

Conclusion

Database management often involves finding creative solutions to handle edge cases and special scenarios. The filtered index feature in SQL Server 2008 and newer versions provides a nice workaround for applying a unique index on a nullable column with multiple null values. It’s yet another tool in the arsenal of a skilled SQL Server developer or DBA.

With that said, in the real world, when I am helping my clients with Comprehensive Database Performance Health Check. I usually do not find many situations where I can create a filtered index and help my client improve performance.

You can connect with me on X (twitter) over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)