David's Blog

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.

Universally Unique Identifiers (UUIDs) make good surrogate keys for a number of reasons:

• They are, by definition, globally unique, leading to practical benefits such as:
  • Given a UUID, it’s possible to identify the relation/tuple (and even potentially the database/environment) for which it is a key. This property can prove useful in the field when troubleshooting.
  • UUIDs will not clash across your environments (unless you want them to).
• They are decentralised. By that I mean that any system is capable of generating a valid UUID. They can, for example, be generated by the middle-tier.
• They are (somewhat) obscure – depending on the version of the algorithm used (usually version 4).
• UUIDs can be generated offline.

UNIQUEIDENTIFIER is the SQL Server type for UUIDs. With SQL Server, there are some practical considerations that you need to weigh up for your scenario:

• UNIQUEIDENTIFIERs require 16 bytes for storage (compared to 4 bytes for an INTEGER).
• INSERTs will be randomly distributed if the UUID is generated via NEWID(). See NEWSEQUENTIALID() for an alternative.
• JOINs and other operators involving UNIQUEIDENTIFIERs will likely perform worse compared to those involving INTEGERs.

Sometimes, the logical advantages of using UUIDs as generated keys in your system can outweigh these practical considerations.

Did you know you can specify an integer following GO and the client tools will submit the batch the specified number of times? For example:

PRINT ‘GO’
GO 3

returns:

Beginning execution loop
GO
GO
GO
Batch execution completed 3 times.

Useful for, among other things, quickly generating test data.

SQL Server 2005 does not provide specific functions for Base64 encoding / decoding, but you can create them easily enough by leveraging the XML functionality.

CREATE FUNCTION [dbo].[base64_encode] (@data VARBINARY(max)) RETURNS VARCHAR(max)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
    RETURN (
        SELECT [text()] = @data
            FOR XML PATH(”)
    )
END
GO

CREATE FUNCTION [dbo].[base64_decode] (@base64_text VARCHAR(max)) RETURNS VARBINARY(max)
    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
    DECLARE @x XML; SET @x = @base64_text
    RETURN @x.value(‘(/)[1]‘, ‘VARBINARY(max)’)
END
GO

If throughput is a concern, equivalent CLR-based implementations would likely have preferable performance characteristics.