Unique constraint only over NON-NULL columns

In MS SQL Server 2008, is it possible to define UNIQU开发者_高级运维E CONSTRAINT on a column BUT which is checked IF and ONLY IF for NON-NULL values. That is, there will be many NULLs which should be ignored. For non-nulls it should be unique, however.

You need to create a Filtered Index:

CREATE UNIQUE NONCLUSTERED INDEX User_PinNr_IUC
ON [User] (pinNr)
WHERE pinNr IS NOT NULL

Refering to CREATE INDEX (Transact-SQL)

WHERE < filter_predicate > :
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.