I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails.

Suppose you need to move some stuff in your car, and you don't want to load more than 100 pounds into your small car at a time. Here is the DDL:

CREATE SCHEMA Data AUTHORIZATION dbo;
GO
CREATE SCHEMA Readers AUTHORIZATION dbo;
GO
CREATE TABLE Data.ItemsToMove(
  TripNumber INT NOT NULL,
  ItemName VARCHAR(50),
  ItemWeightInPounds FLOAT
);
GO
CREATE FUNCTION Readers.TotalWeightPerTrip(@TripNumber INT)
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  WHERE TripNumber = @TripNumber);
END
GO
ALTER TABLE Data.ItemsToMove
  ADD CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds
  CHECK (Readers.TotalWeightPerTrip(TripNumber) <= 100);
GO
  

Loading 90 pounds succeeds, but when you go over the limit, the constraint works:

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 1, 'Big Box with Clothes', 40
UNION ALL
SELECT 1, 'Lumbar Aeron Chair', 25
UNION ALL
SELECT 1, 'Basic Aeron Chair', 25;

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 1, 'Small Box with Books', 40;
  

 Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

So, all other items should be moved on the second trip:

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 2, 'Small Box with Books', 40
UNION ALL
SELECT 2, 'Humidifier', 40;
  

Now there is another problem: all bulky items are scheduled to move on the first trip, while small and hevy ones get on the second trip. Suppose you cannot fit the bulky box with clothes after you loaded your chairs, and want to move your small humidifier on your first trip instead:

UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName IN ('Humidifier', 'Big Box with Clothes') AND TripNumber IN(1,2);  

The update should succeed, because the box and the humidifier have the same weight. However, the update fails:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

The reason is simple: the database engine modifies just one row and immediately invokes the CHECK constraint for that row. At that moment the limit for total load is exceeded no matter which row is modified first - that is the way I made up this example. You can run the following scripts and see for yourself:

ALTER TABLE Data.ItemsToMove
  DROP CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds;

BEGIN TRAN


UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  ELSE TripNumber END
WHERE  ItemName = 'Humidifier' AND TripNumber = 2;


SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;


ROLLBACK;

BEGIN TRAN


UPDATE Data.ItemsToMove 
SET TripNumber = CASE 
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName = 'Big Box with Clothes' AND TripNumber = 1;


SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;


ROLLBACK;
  

 At this point the faulty constraint is dropped, so you can rerun your multi-row update:

UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName IN ('Humidifier', 'Big Box with Clothes') AND TripNumber IN(1,2);   

 Create the constraint again, it will succeed. Verify that the constraint is trusted:

SELECT name, is_not_trusted
FROM   sys.check_constraints
WHERE Name='CHK_TotalWeightPerTrip_LessThan100pounds';
 

You can also verify that your data is clean, that the load for every trip does not exceed 100 pounds:

 SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;

I have been practicing database unit testing for two years, and I am a big fan of it. My test harness is a great help to me - it lets me change my modules quickly and with confidence. However, having too many unit tests is counterproductive - they become a burden and slow you down rather than help you out.Joel Spolski recently wrote a great article about the overhead of maintaining your unit tests being "disproportional to the amount of benefit that you get out of them".

 That article is a must read!

Unit tests are an overhead.

It takes time to develop unit test, and more time to maintain them. They also slow down working with source control and searches. For example, every time your search on a stored procedure name in your solution, every unit test running in shows up in your search results. Yet we go for this overhead, because we expect the payoff from better code quality and easier maintenance to compensate for it. Clearly to break even or benefit from unit tests we need to keep the overhead within reason.

Unit tests take time to run, sometimes too much time.

To be really useful, your unit test harness needs to run quickly - otherwise it will slow you down. You will either spend too much time watching it run, or you will not run it as often as you should - if you break something, you will not notice it right away. Sometimes this becomes a serious problem with C# or C++ solutions. Because database tests usually run much much slower, this may become a serious problem much sooner. In many cases you simply cannot afford to keep all your database unit test because of the sheer amount of time they run. Alternatively, database unit testing can be completely abandoned because of this slowness. It can be dismissed as yet another approach that sounds good in theory but does not hold up in the real world. Database unit testing does hold up in real life projects, but only if you use common sense and do it properly.

Unit tests may help you out if you need to change your module.

They can be extremely useful - you can recall what the module is supposed to do, identify an issue, make a change, and retest in just a few minutes. This is why we need them most of all. So, the more a unit test is potentially useful, the more you want to keep it long term. And, using the same logic, if a unit test is very unlikely to ever help you out, there is no reason to keep it.

Complex modules need unit tests most of all. Trivial tests and tests for simple modules can be eliminated.

Complex queries need more maintenance, because it takes more time to understand and change them, and because they are more likely to need optimization, This is where unit tests come very handy. On the other hand, trivial queries such as the following are easier to change and less likely to need optimization later:

Do you have problems understanding what this procedure does? Do you expect it will be difficult to change it if needed? Do you think this procedure needs an explicit unit test?

Do you really need existence tests?

When we write blog posts and articles, our examples must be short and simple. Running a unit test to determine if your database or your table exists is a clear and simple example and a great way to learn how unit tests work - (make sure you have read this article by SQL Server MVP Andy Leonard). However, there is no need to keep such tests in real production solutions - if a database or a table is dropped, you will get a clear error message, and you will know what is your problem right away. If a database or a table is dropped, you will not need an existence unit test to help you out with troubleshooting, will you?

 Make a distinct database call once, and verify everything.

Database call are slow, so you really want to limit the number of database calls your test harness does. For example, suppose that you have a complex stored procedure Readers.SelectCustomersByName, and if you only provide last name, it must return all the customers with that last name. Because the procedure is complex, you need a unit test for each distinct case of its usage. Because you want to minimize the number of database calls, you want to execute the following command only once:

It is very important to verify everything: number of result sets, number of columns, their names and types, and all the columns and rows returned by the procedure. The reason is simple: any change can be breaking, and you need to determine if you broke something when you do your changes. For more explanations read this article: "Close those Loopholes - Testing Stored Procedures"

I've used the approach described in that article for two years and it works great. Everything is verified automatically, so I never forget to test anything, and I only need one database call for all my checks, so it runs fast.

Clean up after test driven development.

I like and practice test driven development, however I have the following reservations regarding it: all-too-often when you think that are done you have a huge amount of trivial unit tests. That seems to be the whole idea behind test driven development: every step in your progress is a unit test. However, there is no need to keep the whole history of your progress, to keep all the tests that drove your development, for the whole life of your system (each of us humans has had gills and a tail at some early stage of our development; we don't keep them forever, we undergo a refactoring). When you are done with your development, you only need the tests that might help you out in troubleshooting. Most likely you'll need to consolidate your unit tests in the way described in the previous chapter. Remember that test driven development is supposed to consist of the following stages:

1. Encounter a non-trivial problem.
   
2. Write a unit test that fails.
3. Develop some code so that the test passes.
4.  Refactor - test driven development does not stop when your tests pass!

I think that this refactoring should include not only your code but unit tests too. Refactoring is done when your remaining unit tests are lean, so that the overhead of keeping them is justified by the benefits of having them.

Let me repeat myself: from where I sit, test-driven development can be a huge help if you are solving a complex problem. However, it can slow you down if the steps you are taking are too easy for you. I guess I should write up some example of test driven development, but it is getting late, so not this time.

It is well known that nested calls of scalar UDFs are a huge drag on performance, but in many cases inline UDFs can be nested without performance penalty. Here is a simple repro that you can run and see for yourself. Suppose that you already have two inline UDFs: one returning the previous calendar day and another returning the first day of the month, as follows:

CREATE FUNCTION Readers.GetFirstDayOfMonth(@d DATETIME)
RETURNS TABLE AS RETURN(
  SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101') AS FirstDayOfMonth
)
GO
DROP FUNCTION Readers.GetPreviousDay
CREATE FUNCTION Readers.GetPreviousDay(@d DATETIME)
RETURNS TABLE AS RETURN (
  SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', @d) - 1, '19900101') AS PreviousDay
)
GO   

Suppose that you need to develop a UDF returning the last day of the previous month. You can develop it from scratch:

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Merged(@d DATETIME)
RETURNS TABLE AS RETURN(
  SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101')) - 1, '19900101')
    AS LastDayOfPreviousMonth
) 

Alternatively you can reuse your two existing UDFs in several ways, as follows:

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested1(@d DATETIME)
RETURNS TABLE AS RETURN(
  SELECT p.PreviousDay AS LastDayOfPreviousMonth
    FROM Readers.GetFirstDayOfMonth(@d) AS f
    CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonth) AS p   
)
GO
CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested2(@d DATETIME)
RETURNS TABLE AS RETURN(
  SELECT p.PreviousDay AS LastDayOfPreviousMonth
    FROM (SELECT FirstDayOfMonth FROM Readers.GetFirstDayOfMonth(@d) AS f) AS f
    CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonth) AS p   
)   

To benchmark, I used my Data.Numbers table whihc has 1 million rows. You can use any large enough table.

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

-- this table has 1 million rows
SELECT COUNT(*) FROM Data.Numbers

DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Merged(@d);

GO 

DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested1(@d);

GO

DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested2(@d);

GO

-- this measures the overhead of benchmarking, 
-- scanning the table and assigning a value to a variable
DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=@d --INTO #t1
FROM Data.Numbers

 

I did not notice any difference in performance beyond usual noise. Of course, in some very complex cases performance of nested inline UDFs might suffer, but in general this is not the case and usually you can reuse your inline UDFs without performance penalty.

 My previous post about inline UDFs is this: Reuse Your Code with Table-Valued UDFs

I described how correlation between columns may confuse the optimizer and cause it to choose an inefficient plan. I also recommended index covering as the most robust way to optimize queries that involve correlated columns.

However, there are quite a few cases when correlated columns are in different tables. For example,  employees' home zipcodes and their cell phone area codes may be strongly correlated. Similarly, car models and kinds of repair work for those cars may be strongly correlated too.

In such cases indexed views come very handy, because they can allow the optimizer to satisfy such queries with one and the same plan, the range scan, regardless of the expected cardinality. Even if the optimizer's estimated cardinality is wildly wrong, it does not matter if the execution plan is still the same range scan.

Of course, indexed views come with a rather hefty price tag,

In all-too-many cases an indexed view may solve your short term performance goals but at some later time become counterproductive. So if you choose to use an indexed view, you may need an exit strategy. Let me describe a few common problems with indexed views.

Indexed views may increase lock contention.

It is very easy to demonstrate. Create the following table:

CREATE TABLE dbo.ChildTable(ChildID INT NOT NULL 
  CONSTRAINT PK_ChildTable PRIMARY KEY,
  ParentID INT NOT NULL,
  Amount INT NOT NULL);
GO   

From one tab in SSMS, run this script:

BEGIN TRAN;
INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount)
  VALUES(1,1,1); 

From another tab, run a similar one:

BEGIN TRAN;
INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount)
  VALUES(2,1,1);
ROLLBACK;
  

Note that both inserts complete, they do not block each other. Rollback in both tabs, and create an indexed view:

CREATE VIEW dbo.ChildTableTotals WITH SCHEMABINDING
AS
SELECT ParentID, 
  COUNT_BIG(*) AS ChildRowsPerParent, 
  SUM(Amount) AS SumAmount
FROM dbo.ChildTable
GROUP BY ParentID;
GO
CREATE UNIQUE CLUSTERED INDEX ChildTableTotals_CI 
  ON dbo.ChildTableTotals(ParentID);

Rerun the two inserts. Note that the second one does not complete; it is blocked. The reason is very simple: the first insert modifies the corresponding entry in the indexed view, so the insert acquires and holds a lock on it.

It is just as easy to demonstrate that when you create an indexed view, deadlocks may become more likely too.

Note: this is not a problem with the way indexed views are implemented. If you roll out your own summary table, and develop triggers which directly modify it to keep it up-to-date, you will encounter the same problem. Only if you don't maintain your summary table all the time, you can get around this locking problem, but a more detailed discussion of this is beyond the scope of this post.

Indexed views on joins may become counterproductive.

Create the following table and another indexed view:

CREATE TABLE dbo.ParentTable(ParentID INT NOT NULL 
  CONSTRAINT PK_ParentTable PRIMARY KEY,
  WideData CHAR(1000) NOT NULL);
GO

CREATE VIEW dbo.ParentTableWithAmounts WITH SCHEMABINDING
AS
SELECT p.ParentID, p.WideData, c.ChildID, c.Amount
FROM dbo.ParentTable AS p JOIN dbo.ChildTable AS c
  ON p.ParentID = c.ParentID;
GO

CREATE UNIQUE CLUSTERED INDEX ParentTableWithAmounts_CI 
  ON dbo.ParentTableWithAmounts(ChildID);
 

Suppose that originally you have an average one child row per parent on, and that selecting from this indexed view is faster than joining two tables - that's why you created it in the first place. However, if on average you have 10K child rows per parent one, your indexed view becomes counterproductive. Let's add 10K child rows:

INSERT INTO dbo.ParentTable(ParentID, WideData)
  VALUES(1,'asdf');

SET NOCOUNT ON;
DECLARE @i INT;
SET @i=10000;
WHILE @i<20000 BEGIN
  INSERT INTO dbo.ChildTable(ChildID, ParentID, Amount)
    VALUES(@i,1,1);
  SET @i=@i+1;
END
  

Let us select from this indexed view:

SELECT ParentID, WideData,  ChildID,  Amount
  FROM dbo.ParentTableWithAmounts; 

The optimizer has chosen not to use the indexed view, and the execution costs are as follows:

Table 'ChildTable'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParentTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you force the optimizer to use the indexed view,  the execution costs are dramatically higher:

SELECT ParentID, WideData,  ChildID,  Amount
  FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);   

 Table 'ParentTableWithAmounts'. Scan count 1, logical reads 1435, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you have seen, when the parent table is wide and there are many child rows per parent, the indexed view becomes counterproductive, and the optimizer is able to recognize the fact.

Also note that this indexed view increases lock contention just as the previous one did. You can try to update a parent row and one of its child rows and see for yourself.

Exit strategy for NOEXPAND hint

If your indexed view becomes counterproductive, you may consider dropping it altogether. However, dropping the index view will break all those queries with NOEXPAND hint:

DROP INDEX dbo.ParentTableWithAmounts.ParentTableWithAmounts_CI;
GO
SELECT ParentID, WideData,  ChildID,  Amount
  FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);
 

Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ParentTableWithAmounts' is invalid.

I would recommend to be prepared for such possibility, to wrap all your selects using NOEXPAND in stored procedures, for example:

CREATE PROCEDURE dbo.SelectParentTableWithAmounts
AS
SELECT ParentID, WideData,  ChildID,  Amount
  FROM dbo.ParentTableWithAmounts WITH(NOEXPAND);



and to provide a rollback script which alters these procedures, as follows:

DROP INDEX dbo.ParentTableWithAmounts.ParentTableWithAmounts_CI;
GO

ALTER PROCEDURE dbo.SelectParentTableWithAmounts
AS
SELECT ParentID, WideData,  ChildID,  Amount
  FROM dbo.ParentTableWithAmounts; 

You can add error handling to this script, so that either both changes deploy or none does. Make sure to test this script. If you have unit tests, include this scenario in your test harness. Of course there are other approaches, but my main point is that you need to be aware that your indexed view can be dropped, and have a working exit strategy.

Performance considerations for your exit strategy

We have just discussed how to make sure that your application does not break, but what about the performance? Clearly the performance of your selects may plunge, what can be done about it? In many cases, index covering gives you acceptable performance without too much lock contention. Also sometimes you want to roll out your own summary tables, but that sounds like a topic for another post.

Court in New Jersey ordered code review of software used for alcohol breath tests.

Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one is bypassed. Let me provide some examples. I do not intend to provide a comprehensive list of all such cases, I only want to demonstrate that sometimes CATCH blocks are bypassed. Also I would like to emphasize that in almost all the cases CATCH blocks do catch errors, and the exceptions, if any, are quite rare.

KILL command and timeout (aka attention) both stop execution without invoking CATCH blocks.

As a result of a KILL or an attention, the execution stops immediately. You can run the following script, cancel the query or kill it from another tab, and see for yourself:

SELECT @@SPID;

GO

BEGIN TRY

  PRINT 'Before WAITFOR';

  WAITFOR DELAY '00:35:00';

  PRINT 'After WAITFOR';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

You can also invoke it from ADO.Net, and you can also decrease CommandTimeout, so that the timeout occurs faster.

As you have seen, you can only catch such errors on the client.

Note: timeout and lock timeout are different. Lock timeout errors are caught by CATCH blocks.

Some compile errors cancel batch execution.

See for yourself:

BEGIN TRY

  PRINT 'Beginning TRY';

  BEGIN TRAN

  INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

-- make sure #t does not exists for your connection

  DELETE FROM #t;

  COMMIT;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

  ROLLBACK;

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

Beginning TRY

(1 row(s) affected)

Msg 208, Level 16, State 0, Line 6

Invalid object name '#t'.

Next batch

This problem is more likely to happen if you work with temporary tables.  However, because at the time of this writing we have deferred name resolution, you can get this problem with a permanent object too. Surprisingly enough, if you wrap this batch in a stored procedure, and invoke your procedure from another TRY block, the second TRY block does catch the error. You can create the procedure and see for yourself:

CREATE PROCEDURE dbo.TestProc

AS

BEGIN TRY

  PRINT 'Beginning TRY';

  DELETE FROM #t;

  --INSERT data.SomeData(j) SELECT 1;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

BEGIN TRY

  PRINT 'Beginning TRY';

  EXEC dbo.TestProc;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

In my opinion it would be preferable not to start executing a batch if there are compilation errors, and in most cases exactly this is happening:

BEGIN TRY

  PRINT 'Beginning TRY';

  DECLARE @i INT;

  INSERT data.SomeData(j) SELECT 1;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

Msg 207, Level 16, State 1, Line 4

Invalid column name 'j'.

Next batch

If you are still reading this, also make sure you have read Erland Sommarskog’s articles on error handling:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

 and STRICT_CHECKS:

http://www.sommarskog.se/strict_checks.html

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 be very handy too. I will provide examples and hopefully we will come up with Connect items to vote for.

When more complex logical expressions in foreign keys are useful

Consider the following simple common sense rule: the maximum current of your device cannot exceed the maximum current of the circuit you plug it into. Suppose that the following tables store data about circuits and devices:

CREATE TABLE Data.Curcuits(CurcuitID INT NOT NULL

  CONSTRAINT PK_Curcuits PRIMARY KEY,

  MaximumCurrent INT NOT NULL,

  Description VARCHAR(100) NOT NULL);

GO

INSERT INTO Data.Curcuits(CurcuitID,

  MaximumCurrent,

  Description)

SELECT 1, 25, 'Deck and Garage';

GO

CREATE TABLE Data.Devices(DeviceID INT NOT NULL

  CONSTRAINT PK_Devices PRIMARY KEY,

  CurcuitID INT NULL,

  MaximumCurrent INT NOT NULL,

  Description VARCHAR(100) NOT NULL,

  CONSTRAINT FK_Devices_Curcuits FOREIGN KEY(CurcuitID)

    REFERENCES Data.Curcuits(CurcuitID)

  );

GO

It would be very convenient to issue a simple command and implement this business rule:

ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits

  FOREIGN KEY(CurcuitID, MaximumCurrent)

  REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)

 MATCH ON((Data.Devices.CurcuitID = Data.Curcuits.CurcuitID) AND

 (Data.Devices.MaximumCurrent <= Data.Curcuits.MaximumCurrent));

However, it is not supported, so I need to use a workaround, one more column and three constraints instead of one, as follows:

ALTER TABLE Data.Curcuits

  ADD CONSTRAINT UNQ_Curcuits UNIQUE(CurcuitID, MaximumCurrent);

GO

ALTER TABLE Data.Devices ADD CurcuitMaximumCurrent INT NULL;

GO

ALTER TABLE Data.Devices DROP CONSTRAINT FK_Devices_Curcuits;

GO

ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits

  FOREIGN KEY(CurcuitID, CurcuitMaximumCurrent)

  REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)

  ON UPDATE CASCADE;

GO

ALTER TABLE Data.Devices

  ADD CONSTRAINT CHK_Devices_SufficientCurcuitMaximumCurrent

  CHECK(CurcuitMaximumCurrent >= MaximumCurrent);

GO

You can verify that the constraints work:

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 1, 1, 50, 25, 'Electric car charger'

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Devices_SufficientCurcuitMaximumCurrent". The conflict occurred in database "Test", table "data.Devices".

The statement has been terminated.

As you have seen, the implementation of a very simple and very common business rule is quite involved, because such business rules are not directly supported by the database engine.

When you want to create constraints on indexed views

Even when your database guarantees that “the maximum current of your device cannot exceed the maximum current of the circuit you plug it into”, it is not good enough. Consider the following sample data:

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 2, 1, 15, 25, 'ShopVac';

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 3, 1, 15, 25, 'Miter Saw';

The database structure allows to plug more than one device into a circuit, which is correct, but if you turn both devices on, their combined maximum current exceeds the circuit’s maximum current. To enforce this business rule, it would be natural to create an indexed view, so that the database guarantees that the totals are always correct:

CREATE VIEW Data.TotalMaximumCurrentPerCircuit WITH SCHEMABINDING

AS

SELECT d.CurcuitID,

  c.MaximumCurrent AS CircuitMaximumCurrent,

  SUM(d.MaximumCurrent) AS TotalMaximumCurrent,

  COUNT_BIG(*) AS NumDevices

FROM Data.Devices d JOIN Data.Curcuits c ON d.CurcuitID = c.CurcuitID

GROUP BY d.CurcuitID, c.MaximumCurrent;

GO

CREATE UNIQUE CLUSTERED INDEX Data_TotalMaximumCurrentPerCircuit

ON Data.TotalMaximumCurrentPerCircuit(CurcuitID);

GO

If I could create a check constraint on that indexed view, I would be all set:

ALTER VIEW Data.TotalMaximumCurrentPerCircuit

  ADD CONSTRAINT CHK_TotalMaximumCurrentPerCircuit_ValidCurcuit

  CHECK(TotalMaximumCurrent <= CircuitMaximumCurrent)

Instead, I need to use triggers or rather contrived kludges. A built in native support for such quite common business rules would increase the usefulness of SQL Server.

I have not created anything on Connect yet, because the ideas are kind of raw yet. I am very interested in your feedback. What do you think?

This continues the series on denormalizing, which started with this post about working hours and appointments 

and a post about Storing intervals of time with no overlaps

Using ROWVERSION to enforce business rules