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

Revati S Misra

Can you answer this question?

1 Answers

Liked By