SQL SERVER Uniqueness of nullable field
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
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
No comments:
Post a Comment