Assuming you have the table with nullable field. What if you need to support uniqueness on not-null values only? For example, you can have the customers table with optional SSNs – not all customers provide it but if SSN is provided, you want to make sure it’s unique.
Regular index is not an option – SQL Server would not allow you to have more than 1 null value.
SQL Server 2008 provides great feature – filtered index – you will be able to create it on not null fields only.
Now when you insert duplicate value – it fails:
With SQL 2005 it’s not an option. Although you can use the indexed view:
Now when you insert the duplicate, it violates clustered index on the view and fails