Hello,

"Data Integrity" can be enforced in sql server through

PRIMARY KEY Constraint

FOREIGN KEY Constraint

UNIQUE ConstraintCHECK Constraint

Default Definition

so on..

My point here is all about enforcing "Unique Constraint".

This can be done in several ways.

Query 1. Add Unique Constraint

ALTER TABLE dbo.<tablename> ADD CONSTRAINT

<constraint_name> UNIQUE NONCLUSTERED

(

<columnname>

) ON [PRIMARY]

Query 2. Add Unique Index

CREATE UNIQUE NONCLUSTERED INDEX

<index_name> ON dbo.<tablename>

(

<columnname>

) ON [PRIMARY]

Technically there is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same.

Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index also creates index that are physical structure that maintain uniqueness. It is a convenient way to enforce a Unique Constraint for SQL Server.

In above case "Unique Constraint" allows one "NULL".

But my question was how the above Unique Constraint can be created but allowing multiple NULL values and not the RULES of Unique constraint.

I just want to bring that it is possible to enforce Uniqueness in a column but allowing multiple "NULL" values and one of the way to implement this is using filtered index.

Ref: http://blog.sqlauthority.com/2007/04/26/sql-server-difference-between-unique-index-vs-unique-constraint/

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/a298b63b-e1eb-4b31-a2d7-64e1fe493b0a

Thanks

Gopi