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
Email is a nullable field.
So how would you achieve the
Email uniqueness along with having more than one NULLs.
CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].
WHERE Email IS NOT NULL
Above code will ensure that
Email doesn’t have duplicate data however it may store more than one NULLs.
I hope you may have liked the tip. You share your comments.
Row Versioning based Isolation levels are very useful in reducing blocking. Which are.
- Read Committed (default) Isolation level with READ_COMMITTED_SNAPSHOT database option is ON.
- Snapshot Isolation level with the ALLOW_SNAPSHOT_ISOLATION database option is ON.
Read committed isolation using row versioning provides statement-level read consistency however Snapshot isolation provides transaction-level read consistency.
Statement-level read consistency Vs. Transaction-level read consistency
In Statement-level read consistency when each statement within the transaction executes, a new data snapshot is taken and remains consistent for each statement until the statement finishes execution. Enable this when:
- When concurrency benefits is more important than increased overhead of creating and managing row versions.
- An application requires absolute accuracy for long-running queries where data values must be consistent to the point in time when a query starts.
In Transaction-level read consistency, a data snapshot is taken when the snapshot transaction starts, and remains consistent for the duration of the transaction, due to that it provides the benefit of repeatable without using shared locks Use snapshot isolation when:
- Optimistic concurrency control is desired.
- Probability is low that a transaction would have to be rolled back because of an update conflict (As it happens here, when transaction running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with read committed isolation using row versioning).
- An application needs to generate reports based on long-running queries that must have point-in-time consistency.
Hopefully you liked the blog. Please share your comments.