Sometimes we get the data that needs to be unique but can also get NULL records. Existing ways to achieve uniqueness doesn’t allow NULLs (Primary Key) or allow max 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 requirement, email needs to be unique (if supplied), however, users are also allowed to register without entering email and hence in the database
So how would you achieve the
CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].
WHERE Email IS NOT NULL
Above code will ensure that
I hope you may have liked the tip. You share your comments.