Browse by Tags
» Data Integrity (RSS)
Even the simplest data integrity rules are not easy to
implement in the database if instead of individual rows we need to deal with
groups or subsets.
For example, making sure that a column value is unique in a
table is as trivial as creating a unique constraint or index. However, what are
we supposed to do if we do not want to store a ...
To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them, and post your findings.
I've run my scripts on 2008 R2 Dev Edition. Snapshot ...
Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself.
I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, ...
In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save.
The following tables implement a very common type/subtype pattern:
CREATE TABLE dbo.Vehicles( ID INT NOT ...
We can tuck any logical expressions in CHECK constraints,
but all foreign keys are currently capable of right now is to verify that one
or more column values are equal. In many cases it would be very useful to have
foreign keys use more complex logical expressions. Also in some cases the
ability to create constraints on indexed views would ...
I will demonstrate how use constraints to make sure that
intervals of time have no overlaps. Also you may ensure that there are no gaps
either, but if you choose to allow gaps, then the query to retrieve them is
very easy. As discussed in my previous posts, you can also use triggers or UDFs
wrapped in CHECK constraints, but only trusted ...
Calculating running totals is notoriously slow, whether you
do it with a cursor or with a triangular join. It is very tempting to denormalize,
to store running totals in a column, especially if you select it frequently.
However, as usual when you denormalize, you need to guarantee the integrity of
your denormalized data. Fortunately, you can ...
You cannot use CHECK constraints to compare columns in different
rows or in different tables, unless you wrap such functionality in scalar UDFs
invoked form a CHECK constraint. What if you actually need to compare columns
in different rows or in different tables to enforce a business rule? For
example, suppose that you know working hours of ...