David's Blog

This post has been moved to: http://blogs.clarience.com/clarience/?p=31

This post has been moved to: http://blogs.clarience.com/clarience/?p=30

This post has been moved to: http://blogs.clarience.com/clarience/?p=29

Indexed-views are useful for more than just performance improvement, they can also be used to implement constraints. In particular, constraints involving multiple tables.

The principle is relatively simple. Design an indexed-view such that duplicate keys are selected for every constraint violation. Sometimes a “numbers” table can be useful to double-up the rows (although we won’t be using that technique here).

An example using AdventureWorks may be helpful… This is a little contrived but let’s say that we wanted to setup a constraint such that finished goods may be supplied by only one preferred vendor.

First of all, we need to update the data so that the constraint is not violated (it is with the data as supplied):

UPDATE V
    SET PreferredVendorStatus = 0
    FROM Purchasing.Vendor V
        JOIN (
            SELECT P.ProductID, VendorID = MIN(V.VendorID)
                FROM Production.Product P
                    JOIN Purchasing.ProductVendor PV ON P.ProductID = PV.ProductID
                    JOIN Purchasing.Vendor V ON PV.VendorID = V.VendorID
                WHERE P.FinishedGoodsFlag = 1
                    AND V.ActiveFlag = 1
                    AND V.PreferredVendorStatus = 1
                GROUP BY P.ProductID
                HAVING COUNT(*) > 1
        ) U ON V.VendorID = U.VendorID

Then we create an indexed-view to represent the constraint:

CREATE VIEW Purchasing.OnePreferredVendorForFinishedGoods
WITH SCHEMABINDING
AS
SELECT P.ProductID, V.VendorID, _Count = COUNT_BIG(*)
    FROM Production.Product P
        JOIN Purchasing.ProductVendor PV ON P.ProductID = PV.ProductID
        JOIN Purchasing.Vendor V ON PV.VendorID = V.VendorID
    WHERE P.FinishedGoodsFlag = 1
        AND V.ActiveFlag = 1
        AND V.PreferredVendorStatus = 1
    GROUP BY P.ProductID, V.VendorID

If a particular ProductID occurs more than once in the view output, it means we have a finished product that it is supplied by more than one preferred vendor. We add a unique index to prevent such an occurrence:

CREATE UNIQUE CLUSTERED INDEX IX__OnePreferredVendorForFinishedGoods__ProductID ON Purchasing.OnePreferredVendorForFinishedGoods (ProductID)

Now, let’s try to violate the constraint:

UPDATE Purchasing.Vendor
    SET PreferredVendorStatus = 1
    WHERE VendorID = 55

We see the following:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘Purchasing.OnePreferredVendorForFinishedGoods’ with unique index ‘IX__OnePreferredVendorForFinishedGoods__ProductID’.
The statement has been terminated.

This is a powerful technique for implementing complicated constraints. Another good thing is that this works in all editions, even Express Edition.

I’ve encountered more than a few SQL Server based applications where deadlocks are treated as an error in almost exactly the same manner as say, a .NET null-reference exception. In most cases this means the user sees a message along the lines of: “An error has occurred, please try again”.

A deadlock is usually a recoverable condition, so this is the wrong behaviour, at least at first. Any concurrency scheme where a “thread” is able to block waiting on a lock while holding other lock(s) is potentially subject to deadlock conditions. Deadlocks and pessimistic concurrency go hand-in-hand.

More often than not a simple retry of the rolled-back transaction will succeed. An error should be allowed to bubble-up to the user only after a specified number of retries; three retries is often reasonable.

This does not mean to say that you shouldn’t endeavour to limit deadlocks in your SQL Server applications, of course you should. Rolling-back and retrying a transaction is an expensive operation that you want to avoid if at all possible but the point is, don’t give up after the first attempt.