Unique constraint to allow multiple NULL's in Sql server

Unique constraint

Creating Unique constraint as NONCLUSTERED INDEX will allow the values are entered in a column of a table to be unique. Therefore only one NULL value can be allowed to be entered in a column.

However, you often get requirements when a column having unique constraint should allow the multiple NULLs ( not the values).

For example email address column in the login users table of public websites where email address should be unique and should all multiple NULLs to be inserted for non internet users.

Changing Unique constraint to allows multiple NULLs in Sql server:

In SQL2008, you can define a unique filtered index based on a predicate that excludes NULLs.

Syntax:

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

Here,

idx_column_notnull is the constraint name.

table_name is the table name.

column_name is the column name.

WHERE clause is to define the predicate to exclude what?