With SQL Server I want a unique index on only the non-null values

Question

Aryan Kumar · Answer

Sure. You can create a unique index on only the non-null values in a column in SQL Server using a filtered unique index. A filtered unique index is a unique index that only applies to rows where the specified column is not NULL.

To create a filtered unique index, you use the CREATE UNIQUE INDEX statement with the WHERE clause. The syntax for the CREATE UNIQUE INDEX statement with the WHERE clause is as follows:

SQL

CREATE UNIQUE INDEX indename
ON table_name (column_name)
WHERE column_name IS NOT NULL;

For example, to create a unique index on only the non-null values in the customer_id column in the customers table, you would use the following code:

SQL

CREATE UNIQUE INDEX unique_null_column
ON customers (customer_id)
WHERE customer_id IS NOT NULL;

This will create a unique index on the customer_id column in the customers table. However, it will only apply to rows where the customer_id column is not NULL. This will prevent you from inserting duplicate null values into the customer_id column.

Here are some additional points to keep in mind when creating a filtered unique index:

The WHERE clause must be a Boolean expression that evaluates to TRUE or FALSE.
The WHERE clause cannot reference any other columns in the index.
The WHERE clause cannot reference any columns that are not part of the index.
The WHERE clause cannot reference any columns that are nullable.

forum

With SQL Server I want a unique index on only the non-null values

Can you answer this question?

1 Answers