Sometimes, we get data that needs to be unique but we can also get NULL records. Existing ways to achieve uniqueness don’t allow NULLs (Primary Key) or allow a maximum of one NULL (Unique Constraint).
Let’s take an example to understand this better. Suppose, you have an Employee table with fields like Id, FirstName, MiddleName, LastName, Email, etc. As per the requirement, the email needs to be unique (if supplied), however, users are also allowed to register without entering their email and hence in the database Email is a nullable field.
So, how would you achieve the Email uniqueness having more than one NULL?
- CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].Employee WHERE Email IS NOT NULL
The above code will ensure that the Email doesn’t have duplicate data, however, it may store more than one NULL.
I hope you have liked the tip. Please share your comments.