Search results matching tag 'Data Integrity'

Using constraints to enforce uniqueness of ordered sets of rows

Alexander Kuznetsov — Mon, 28 Jan 2013 20:10:00 GMT

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 group of values twice? What if we store cooking recipes as sequences of steps (sets of values), and we do not want to store one and the same recipe (one set of values) more than once?

In the following sections we shall solve this problem using only constraints and indexed views, just for fun, as a SQL puzzle. (Of course, we could use a trigger or some other thing with subqueries - but that would be a different puzzle).

At the time of this writing I have not yet used in production the technique I am going to describe. I think it is quite complex and looks tricky, and the reason is simple: in my opinion RDBMS have not been designed to deal with this kind of problems. As such, we have to come up with complex workarounds.

Enforcing Uniqueness of Ordered Sets, of Limited Size Only

In this solution we shall deal with sets that have at most five elements.

In some cases, the order of elements in a subset matters. For example, the following two sequences of instructions yield different results.

Cooking a hard-boiled egg:

Put eggs into pan, add water
Bring water to boiling, boil 4 minutes;
Remove eggs and cool them down in cold water

Keeping the egg uncooked:

Put eggs into pan, add water
Remove eggs and cool them down in cold water
Bring water to boiling, boil 4 minutes

These two recipes are clearly different, and they yield different results. Of course, the second recipe makes no sense, but we want the database to store them both.

Suppose, however, that the database should not be able to store one and the same recipe more than once.

Setting up tables and test data

Before implementing this requirement, let us create the tables and add test data. Both recipes discussed above involve the same three steps:

CREATE TABLE dbo.RecipeSteps
    (
      RecipeStepId INT NOT NULL ,
      Intsructions VARCHAR(50) NOT NULL ,
      CONSTRAINT PK_RecipeSteps PRIMARY KEY ( RecipeStepId )
    ) ;
GO
INSERT  INTO dbo.RecipeSteps
        ( RecipeStepId, Intsructions )
VALUES  ( 1, 'Put eggs into pan, add water' ),
        ( 2, 'Bring water to boiling, boil 4 minutes' ),
        ( 3, 'Remove eggs and cool them down in cold water' ) ;

Let us upload upload the first recipe, as follows:

CREATE TABLE dbo.RecipesStepByStep
    (
      RecipeId INT NOT NULL ,
      StepNumber SMALLINT NOT NULL ,
      RecipeStepId INT NOT NULL ,
      CONSTRAINT PK_RecipesStepByStep PRIMARY KEY ( RecipeId, StepNumber ) ,
      CONSTRAINT FK_RecipesStepByStep_RecipeSteps FOREIGN KEY ( RecipeStepId ) REFERENCES dbo.RecipeSteps ( RecipeStepId )
    ) ;
GO

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 1, 1, 1 ),
        ( 1, 2, 2 ),
        ( 1, 3, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 1 ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
2          Bring water TO boiling, boil 4 minutes
3          Remove eggs AND cool them down IN cold water

Let us also add the second recipe:

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 2, 1, 1 ),
        ( 2, 2, 3 ),
        ( 2, 3, 2 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 2 ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
2          Remove eggs AND cool them down IN cold water
3          Bring water TO boiling, boil 4 minutes

Right now nothing prevents us from adding a second copy of the first recipe. You can run the following script and see for yourself that it succeeds:

To enforce uniqueness of recipes, we shall use an indexed view and a unique index on top of it.

To keeps the scripts simple, let us assume that recipies cannot consist of more than five steps:

ALTER TABLE dbo.RecipesStepByStep
ADD CONSTRAINT RecipesStepByStepMax5 CHECK (StepNumber BETWEEN 1 AND 5) ;

Let us also create an indexed view that stores all the steps of a recipe in one row.

Once we have all the steps in one row, we can use a unique index to make sure that a sequence of steps is unique. We want the contents of the indexed view to look as follows, with the first line representing the first recipe:

RecipeId Step1 Step2 Step3 Step4 Step5 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 2 3 0 0 2 1 3 2 0 0

Note: To get around the limitations of indexed views, we need to make sure that all the pivoted columns are not nullable. This is why columns Step4 and Step5 are zeroes instead of nulls – otherwise we would not be able to create the unique index RecipesStepByStepsPivoted_UniqueRecipes in the following script:

CREATE VIEW dbo.RecipesStepByStepsPivoted WITH SCHEMABINDING AS SELECT RecipeId, count_big(*) AS Cnt, SUM(CASE WHEN StepNumber = 1 THEN RecipeStepId ELSE 0 END) AS Step1, SUM(CASE WHEN StepNumber = 2 THEN RecipeStepId ELSE 0 END) AS Step2, SUM(CASE WHEN StepNumber = 3 THEN RecipeStepId ELSE 0 END) AS Step3, SUM(CASE WHEN StepNumber = 4 THEN RecipeStepId ELSE 0 END) AS Step4, SUM(CASE WHEN StepNumber = 5 THEN RecipeStepId ELSE 0 END) AS Step5 FROM dbo.RecipesStepByStep GROUP BY RecipeId ; GO CREATE UNIQUE CLUSTERED INDEX RecipesStepByStepsPivoted_CI ON dbo.RecipesStepByStepsPivoted(RecipeId) ; GO CREATE UNIQUE INDEX RecipesStepByStepsPivoted_UniqueRecipes ON dbo.RecipesStepByStepsPivoted(Step1, Step2, Step3, Step4, Step5) ; GO SELECT RecipeId , Step1 , Step2 , Step3 , Step4 , Step5 FROM dbo.RecipesStepByStepsPivoted ; RecipeId Step1 Step2 Step3 Step4 Step5 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 2 3 0 0 2 1 3 2 0 0

It is easy to verify that the unique index built on top of the indexed view does enforce the uniqueness of recipes:

BEGIN TRANSACTION ;

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 3, 1, 1 ),
        ( 3, 2, 2 ),
        ( 3, 3, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 3 ;

ROLLBACK ;

Cannot INSERT duplicate KEY row IN object 'dbo.RecipesStepByStepsPivoted' WITH UNIQUE INDEX 'RecipesStepByStepsPivoted_UniqueRecipes'.
The statement has been terminated.

RecipeId    Step1       Step2       Step3       Step4       Step5
----------- ----------- ----------- ----------- ----------- -----------
1           1           2           3           0           0
2           1           3           2           0           0

Note: because we are using an undex to enforce uniqueness, we must set a limit on the number of elements. It does not have to be as low as five – we set it that low just to keep the examples short.

Let us close a couple of loopholes in this limited solution and move on to another problem.

First, we used zeroes in the indexed view to indicate that there is no such step in the recipe. In our example both recipies had three steps, so the indexed view had zeroes in Step4 and Step5 columns in both rows. To distinguish between missing steps and valid step IDs, we need to make sure that zero is not a valid ID:

ALTER TABLE dbo.RecipeSteps
ADD CONSTRAINT CHK_RecipeSteps_PositiveId CHECK ( RecipeStepId > 0 );

Also we need to make sure that step numbers in the recipe have no gaps, otrherwise we still can store the same recipe twice. The following script demonstrates the loophole:

BEGIN TRANSACTION ;

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 3, 1, 1 ),
        ( 3, 3, 2 ),
        ( 3, 5, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 3 ;

ROLLBACK ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
3          Bring water TO boiling, boil 4 minutes
5          Remove eggs AND cool them down IN cold water

To close the loophole, we need to make sure that step numbers have no gaps. The following script enforces the rule:

ALTER TABLE dbo.RecipesStepByStep
ADD PreviousStepNumber AS CAST(CASE WHEN StepNumber > 1 THEN StepNumber-1 END AS SMALLINT) PERSISTED ;
GO

ALTER TABLE dbo.RecipesStepByStep
ADD CONSTRAINT FK_RecipesStepByStep_PreviousStep
FOREIGN KEY(RecipeId, PreviousStepNumber)
REFERENCES dbo.RecipesStepByStep(RecipeId, StepNumber);

Conclusion

As we have seen, enforcing the uniqueness of ordered groups of rows with constraints is doable, although somewhat complex. I am not sure if I would use this approach with a large production table. I think that RDBMS is not designed to natively solve these kinds of problems - this is why solving this apparently simple problem is so complicated.

If you are using some platform other than RDBMS to solve such problems, please comment this post and share your experiences.

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

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.

Environment

I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled.

Test data

We are using the same test data as in my previous post:

CREATE TABLE dbo.Toggle1
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        ( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        ( id, i )
VALUES  ( 1, 0 ) ;

Benchmarking

The following two stored procedures run the same modifications 10K times. The first one uses XACT_ABORT ON to ensure atomicity of the transaction:

CREATE PROCEDURE dbo.Toggle10kTimesWithXactAbortOn AS BEGIN; SET NOCOUNT ON ; SET XACT_ABORT ON ; DECLARE @i INT ; SET @i = 0 ; WHILE @i < 10000 BEGIN ; BEGIN TRAN ; UPDATE dbo.Toggle1 SET i = 1 - i WHERE id = 1 ; UPDATE dbo.Toggle2 SET i = 1 - i WHERE id = 1 ; COMMIT ; SET @i = @i + 1 ; END ; END ;The second procedure uses a ROLLBACK in a CATCH block:

CREATE PROCEDURE dbo.Toggle10kTimesWithTryCatch AS BEGIN; SET NOCOUNT ON ; DECLARE @i INT ; SET @i = 0 ; WHILE @i < 10000 BEGIN ; BEGIN TRY ; BEGIN TRAN ; UPDATE dbo.Toggle1 SET i = 1 - i WHERE id = 1 ; UPDATE dbo.Toggle2 SET i = 1 - i WHERE id = 1 ; COMMIT ; END TRY BEGIN CATCH ; ROLLBACK ; END CATCH ; SET @i = @i + 1 ; END ; END ;

Benchmarking

Let us run these two procedures several times:

EXEC dbo.Toggle10kTimesWithXactAbortOn ; GO EXEC dbo.Toggle10kTimesWithTryCatch ;

Typical results are as follows:

dbo.Toggle10kTimesWithXactAbortOn

CPU: 265-280

dbo.Toggle10kTimesWithTryCatch

CPU: 406-468

Conclusion

As we have seen, sometimes using XACT_ABORT ON may use less CPU as opposed to using TRY...CATCH.

I am and not making any blanket statements here - you are encouraged to run your own benchmarks and see if you are observing this effect in your situation. If you do so, can you do us all a favor and post your findings.

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

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.

Environment

I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled.

Test data

After applying Occum's razor, all we need is two tables with one row in each:

CREATE TABLE dbo.Toggle1 ( id INT NOT NULL PRIMARY KEY , i INT NOT NULL ) ; GO INSERT INTO dbo.Toggle1 ( id, i ) VALUES ( 1, 0 ) ; GO CREATE TABLE dbo.Toggle2 ( id INT NOT NULL PRIMARY KEY , i INT NOT NULL ) ; GO INSERT INTO dbo.Toggle2 ( id, i ) VALUES ( 1, 0 ) ;

Benchmarking

The following two stored procedures run the same modifications 10K times. The first one does not use an explicit transaction:

CREATE PROCEDURE dbo.Toggle50kTimesX3WithoutTransaction AS BEGIN; SET NOCOUNT ON ; SET XACT_ABORT OFF ; DECLARE @i INT ; SET @i = 0 ; WHILE @i < 50000 BEGIN ; UPDATE dbo.Toggle1 SET i = 1 - i WHERE id = 1 ; UPDATE dbo.Toggle2 SET i = 1 - i WHERE id = 1 ; SET @i = @i + 1 ; END ; END ;

The second procedure does use an explicit transaction:

CREATE PROCEDURE dbo.Toggle50kTimesX3WithXactAbortOff AS BEGIN; SET NOCOUNT ON ; SET XACT_ABORT OFF ; DECLARE @i INT ; SET @i = 0 ; WHILE @i < 50000 BEGIN ; BEGIN TRAN ; UPDATE dbo.Toggle1 SET i = 1 - i WHERE id = 1 ; UPDATE dbo.Toggle2 SET i = 1 - i WHERE id = 1 ; COMMIT ; SET @i = @i + 1 ; END ; END ;

Let's run the following script a few times:

EXEC dbo.Toggle50kTimesX3WithoutTransaction ; GO EXEC dbo.Toggle50kTimesX3WithXactAbortOff ;

Typical CPU and duration is as follows:

dbo.Toggle50kTimesX3WithoutTransaction

CPU: 1900-2340 Duration:5900

dbo.Toggle50kTimesX3WithXactAbortOff

CPU: 1500-1700 Duration: 3200-3300

Conclusion

As we have seen, wrapping related changes in as transaction may improve performance.By related, I mean changes that should all succeed or all fail if we are ensuring atomicity.

However, we typically need to keep transactions short, so we should not bundle unrelated changes in transactions - that may hurt concurrency.

Also I am being very careful and not making any blanket statements here. I did not research this effect with due diligence. I noticed the effect in one case and came up with a repro that works in one particular case on one server, this is all.

I would really appreciate if you run the repro, tweak it as you see needed, and post the results here. TIA!

In general, we should not assume that larger transactions always mean better performance - the opposite is true in many cases. We should always benchmark the specific case at hand and decide on case by case basis.

Edit: I have simplified the scripts a little bit, and that changed the numbers a little bit.

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

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.

False Negatives

The following tables implement a very common type/subtype pattern:

CREATE TABLE dbo.Vehicles( ID INT NOT NULL, [Type] VARCHAR(5) NOT NULL, CONSTRAINT Vehicles_PK PRIMARY KEY(ID), CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]), CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck')) ); GO CREATE TABLE dbo.Cars(ID INT NOT NULL, [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED, OtherData VARCHAR(10) NULL, CONSTRAINT Cars_PK PRIMARY KEY(ID), CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type]) REFERENCES dbo.Vehicles(ID, [Type]) ); GO -- adding parent rows INSERT INTO dbo.Vehicles(ID, [Type]) VALUES(1, 'Car'), (2, 'Truck'); The following INSERT succeeds:

INSERT INTO dbo.Cars(ID, OtherData) VALUES(1, 'Some Data'); DELETE FROM dbo.Cars;

MERGE should be able to add the same row, but it fails:

MERGE dbo.Cars AS TargetTable USING ( SELECT 1 AS ID , 'Some Data' AS OtherData ) AS SourceData ON SourceData.ID = TargetTable.ID WHEN NOT MATCHED THEN INSERT (ID, OtherData) VALUES(SourceData.ID, SourceData.OtherData); Msg 547, LEVEL 16, State 0, Line 1 The MERGE statement conflicted WITH the FOREIGN KEY CONSTRAINT "Cars_FK_Vehicles" The conflict occurred IN DATABASE "Test" TABLE "dbo.Vehicles" The statement has been terminated.

False Positives

Martin Smith has posted this repro on stackoverflow, in response to my scenario described above. This script successfully adds an orphan row into dbo.Cars table:

ALTER TABLE dbo.Vehicles DROP CONSTRAINT Vehicles_CHK_ValidTypes ; GO INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, ''); GO MERGE dbo.Cars AS TargetTable USING ( SELECT 3 AS ID , 'Some Data' AS OtherData ) AS SourceData ON SourceData.ID = TargetTable.ID WHEN NOT MATCHED THEN INSERT (ID, OtherData) VALUES(SourceData.ID, SourceData.OtherData); GO -- the FK constraint is enabled and trusted SELECT is_disabled,is_not_trusted FROM sys.foreign_keys WHERE name = 'Cars_FK_Vehicles'; -- yet we have an orphan row SELECT * FROM dbo.Cars SELECT * FROM dbo.Vehicles GO is_disabled is_not_trusted ----------- -------------- 0 0 (1 row(s) affected) ID TYPE OtherData ----------- ----- ---------- 3 Car SOME Data (1 row(s) affected) ID TYPE ----------- ----- 1 Car 2 Truck 3 Conclusion

In Ch 7 of my book "Defensive Database Programming", I wrote the following statement.

"As long as a constraint is trusted, then we know for sure that all our data complies with the rules that it dictates."

At the time I was writing that, I believed that was true. Unfortunately, as we have just seen, I was wrong - we can bypass trusted foreign key constraints in SQL 2008 R2.

P.S.

I have just learned that there is another scenario to bypass a foreign key. Google up this: "Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations".

Another very important thing that I have just learned that this Connect item did not get urgent priority. Quoting directly from Connect: "Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue".

Two suggestions to enhance constraints in SQL Server

Alexander Kuznetsov — Tue, 05 May 2009 03:29:00 GMT

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);

INSERT INTO Data.Curcuits(CurcuitID,

MaximumCurrent,

Description)

SELECT 1, 25, 'Deck and Garage';

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)

);

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);

ALTER TABLE Data.Devices ADD CurcuitMaximumCurrent INT NULL;

ALTER TABLE Data.Devices DROP CONSTRAINT FK_Devices_Curcuits;

ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits

FOREIGN KEY(CurcuitID, CurcuitMaximumCurrent)

REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)

ON UPDATE CASCADE;

ALTER TABLE Data.Devices

ADD CONSTRAINT CHK_Devices_SufficientCurcuitMaximumCurrent

CHECK(CurcuitMaximumCurrent >= MaximumCurrent);

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

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;

CREATE UNIQUE CLUSTERED INDEX Data_TotalMaximumCurrentPerCircuit

ON Data.TotalMaximumCurrentPerCircuit(CurcuitID);

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 continued with this post about running totals.

and a post about Storing intervals of time with no overlaps

Using ROWVERSION to enforce business rules

Storing intervals of time with no overlaps.

Alexander Kuznetsov — Mon, 09 Mar 2009 00:12:00 GMT

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 constraints can guarantee that all your data is always 100% clean.

Here is the table and the first interval

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

IntValue INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

PreviousFinishedAt DATETIME NULL,

CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

FOREIGN KEY(SettingID, PreviousFinishedAt)

REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 1, '20070101', '20070103', NULL);

It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.

There can be only one first interval for a setting

The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint guarantees that there can be only one such row per setting. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 1, '20070104', '20070105', NULL);

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

Next window must begin after the end of the previous one.

The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 2, '20070104', '20070109', '20070105')

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

Two different windows cannot refer to one and the same window as their previous one.

Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 3, '20070104', '20070115', '20070103')

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

This means that there can be no overlaps.

As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.

Working with gaps.

You can prohibit gaps altogether, just replace the following constraint:

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

With a stricter one, as follows:

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

But if you allow gaps, the query to retrieve them is very simple and performant, as follows:

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd

FROM dbo.IntegerSettings

WHERE StartedAt > PreviousFinishedAt;

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

and continued with this post about running totals.

Denormalizing to enforce business rules: Running Totals

Alexander Kuznetsov — Fri, 23 Jan 2009 19:57:00 GMT

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 guarantee the integrity of running totals with constraints – as long as all your constraints are trusted, all your running totals are correct. Also this way you can easily ensure that the current balance (running totals) is never negative - enforcing by other methods can also be very slow. The following script demonstrates the technique.

CREATE TABLE Data.Inventory(InventoryID INT NOT NULL IDENTITY,

ItemID INT NOT NULL,

ChangeDate DATETIME NOT NULL,

ChangeQty INT NOT NULL,

TotalQty INT NOT NULL,

PreviousChangeDate DATETIME NULL,

PreviousTotalQty INT NULL,

CONSTRAINT PK_Inventory PRIMARY KEY(ItemID, ChangeDate),

CONSTRAINT UNQ_Inventory UNIQUE(ItemID, ChangeDate, TotalQty),

CONSTRAINT UNQ_Inventory_Previous_Columns UNIQUE(ItemID, PreviousChangeDate, PreviousTotalQty),

CONSTRAINT FK_Inventory_Self FOREIGN KEY(ItemID, PreviousChangeDate, PreviousTotalQty)

REFERENCES Data.Inventory(ItemID, ChangeDate, TotalQty),

CONSTRAINT CHK_Inventory_Valid_TotalQty CHECK(TotalQty >= 0 AND (TotalQty = COALESCE(PreviousTotalQty, 0) + ChangeQty)),

CONSTRAINT CHK_Inventory_Valid_Dates_Sequence CHECK(PreviousChangeDate < ChangeDate),

CONSTRAINT CHK_Inventory_Valid_Previous_Columns CHECK((PreviousChangeDate IS NULL AND PreviousTotalQty IS NULL)

OR (PreviousChangeDate IS NOT NULL AND PreviousTotalQty IS NOT NULL))

);

-- beginning of inventory for item 1

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

VALUES(1, '20090101', 10, 10, NULL, NULL);

-- cannot begin the inventory for the second time for the same item 1

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

VALUES(1, '20090102', 10, 10, NULL, NULL);

Msg 2627, Level 14, State 1, Line 10

Violation of UNIQUE KEY constraint 'UNQ_Inventory_Previous_Columns'. Cannot insert duplicate key in object 'Data.Inventory'.

The statement has been terminated.

-- add more

DECLARE @ChangeQty INT;

SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090103', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

SET @ChangeQty = 3;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090104', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

SET @ChangeQty = -4;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090105', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

-- try to violate chronological order

SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20081231', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

Msg 547, Level 16, State 0, Line 4

The INSERT statement conflicted with the CHECK constraint "CHK_Inventory_Valid_Dates_Sequence". The conflict occurred in database "Test", table "Data.Inventory".

The statement has been terminated.

SELECT ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty

FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty

----------------------- ----------- ----------- ----------------------- -----

2009-01-01 00:00:00.000 10 10 NULL NULL

2009-01-03 00:00:00.000 5 15 2009-01-01 00:00:00.000 10

2009-01-04 00:00:00.000 3 18 2009-01-03 00:00:00.000 15

2009-01-05 00:00:00.000 -4 14 2009-01-04 00:00:00.000 18

-- try to change a single row, all updates must fail

UPDATE Data.Inventory SET ChangeQty = ChangeQty + 2 WHERE InventoryID = 3;

UPDATE Data.Inventory SET TotalQty = TotalQty + 2 WHERE InventoryID = 3;

-- try to delete not the last row, all deletes must fail

DELETE FROM Data.Inventory WHERE InventoryID = 1;

DELETE FROM Data.Inventory WHERE InventoryID = 3;

-- the right way to update

DECLARE @IncreaseQty INT;

SET @IncreaseQty = 2;

UPDATE Data.Inventory SET ChangeQty = ChangeQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN @IncreaseQty ELSE 0 END,

TotalQty = TotalQty + @IncreaseQty,

PreviousTotalQty = PreviousTotalQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN 0 ELSE @IncreaseQty END

WHERE ItemID = 1 AND ChangeDate >= '20090103';

SELECT ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty

FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty

----------------------- ----------- ----------- ----------------------- ----------------

2009-01-01 00:00:00.000 10 10 NULL NULL

2009-01-03 00:00:00.000 7 17 2009-01-01 00:00:00.000 10

2009-01-04 00:00:00.000 3 20 2009-01-03 00:00:00.000 17

2009-01-05 00:00:00.000 -4 16 2009-01-04 00:00:00.000 20

This continues the series on denormalizing, which started with this post.

Denormalizing to enforce business rules: Part 1.

Alexander Kuznetsov — Wed, 21 Jan 2009 23:49:00 GMT

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 a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Note: for more about trusted constraints read a post by SQL Server MVP Hugo Kornelis.

The table storing working hours has one additional constraint which will be used to make sure that all appointments fit within working hours, as follows:

CREATE Table Data.WorkingHours(WorkingHoursID INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

ProfessionalsName VARCHAR(50),

CONSTRAINT PK_WorkingHours PRIMARY KEY(WorkingHoursID),

-- The purpose of UNQ_WorkingHours is as follows:

-- a FOREIGN KEY constraint from a child table will refer to it.

CONSTRAINT UNQ_WorkingHours UNIQUE(WorkingHoursID, StartedAt, FinishedAt),

CONSTRAINT CHK_WorkingHours_ValidWindow CHECK(StartedAt < FinishedAt)

);

SET NOCOUNT ON;

INSERT Data.WorkingHours(WorkingHoursID,

ProfessionalsName,

StartedAt,

FinishedAt)

SELECT 1, 'Jane He', '20061025 08:00AM', '20061025 05:00PM' UNION ALL

SELECT 2, 'Jane He', '20061026 08:30AM', '20061026 05:00PM' UNION ALL

SELECT 3, 'Jane He', '20061027 08:30AM', '20061027 05:00PM';

Now I am going to store the beginning and ending of working hours (that is StartedAt and FinishedAt columns) along with every appointment, so that I can use a CHECK constraint to compare them with the beginning and ending of appointments, as follows:

CREATE Table Data.Appointments ( WorkingHoursID INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

WorkingHourStartedAt DATETIME NOT NULL,

WorkingHourFinishedAt DATETIME NOT NULL,

CONSTRAINT FK_Appointments_WorkingHours

FOREIGN KEY(WorkingHoursID, WorkingHourStartedAt, WorkingHourFinishedAt)

REFERENCES Data.WorkingHours(WorkingHoursID, StartedAt, FinishedAt) ON UPDATE CASCADE,

CONSTRAINT CHK_Appointments_ValidWindow CHECK(StartedAt < FinishedAt),

CONSTRAINT CHK_TimesInRange

CHECK((StartedAt BETWEEN WorkingHourStartedAt AND WorkingHourFinishedAt)

AND (FinishedAt BETWEEN WorkingHourStartedAt AND WorkingHourFinishedAt))

);

Clearly storing two columns from the parent table in the child one is denormalization. However, my FOREIGN KEY constraint guarantees that the columns from the parent table always match the corresponding ones in the child one. On top of that, the ON UPDATE CASCADE clause allows me to modify the values in the parent table, and these changes are propagated into the child one automatically. I will demonstrate it soon. The following inserts succeed, because the appointments fit into business hours:

INSERT Data.Appointments(WorkingHoursID,

StartedAt,

FinishedAt,

WorkingHourStartedAt,

WorkingHourFinishedAt)

SELECT 1, '20061025 09:00AM', '20061025 05:00PM', '20061025 08:00AM', '20061025 05:00PM' UNION ALL

SELECT 2, '20061026 08:30AM', '20061026 01:00PM', '20061026 08:30AM', '20061026 05:00PM' UNION ALL

SELECT 3, '20061027 10:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM';

You can change appointments and working hours as long as appointments still fit, for example:

UPDATE Data.Appointments SET StartedAt = '20061025 08:30AM'

WHERE WorkingHoursID = 1;

UPDATE Data.WorkingHours SET StartedAt = '20061027 09:31AM'

WHERE WorkingHoursID = 3;

UPDATE Data.WorkingHours SET FinishedAt = '20061026 04:31PM'

WHERE WorkingHoursID = 2;

Note that the changes in WorkingHours table were propagated into the child table automatically:

SELECT StartedAt,

FinishedAt,

WorkingHourStartedAt,

WorkingHourFinishedAt

FROM Data.Appointments;

StartedAt FinishedAt WorkingHourStartedAt WorkingHourFinishedAt

----------------------- ----------------------- ----------------------- -----------------------

2006-10-25 08:30:00.000 2006-10-25 17:00:00.000 2006-10-25 08:00:00.000 2006-10-25 17:00:00.000

2006-10-26 08:30:00.000 2006-10-26 13:00:00.000 2006-10-26 08:30:00.000 2006-10-26 16:31:00.000

2006-10-27 10:30:00.000 2006-10-27 13:00:00.000 2006-10-27 09:31:00.000 2006-10-27 17:00:00.000

However, you cannot extend your appointment beyond working hour nor you can shorten your working hours so that some appointment no longer fits:

-- all fail: range not completely inside the parent range

INSERT Data.Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM');

INSERT Data.Appointments VALUES(3, '20061027 10:30AM', '20061027 08:00PM', '20061027 08:30AM', '20061027 05:00PM');

-- fails because there is an appoinment starting at 10:30

UPDATE Data.WorkingHours SET StartedAt = '20061027 10:31AM' WHERE WorkingHoursID = 3;

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

Msg 547, Level 16, State 0, Line 5

The UPDATE statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

In many cases constraints also perform better than triggers, but this is another story…

In the next post I am discussing running totals