THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Denormalizing to enforce business rules: Running Totals

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

);

GO

-- 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.
Published Friday, January 23, 2009 2:57 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov : Denormalizing to enforce business rules: Part 1. said:

January 25, 2009 9:57 AM
 

Adam Machanic said:

Hi Alex,

I don't know why you would do this; if you use a cursor--especially via a SQLCLR routine--it's really not at all slow to calculate these on the fly.  But if you ask me, this is really something that should be done client side...

January 26, 2009 12:09 PM
 

Alexander Kuznetsov said:

Hi Adam,

This way is definitely faster to select, especially for long time series. Also I have simpler code for my SELECTs. Also suppose that you want to make sure that the current balance never goes below 0, which in many cases is a very reasonable business rule. If you enforce it on the client, you can have dirty data and not know about it. If data integrity is of high priority, nothing can beat business rules implemented as trusted constraints - only trusted constraints 100% guarantee that all your data is always valid.

January 26, 2009 1:32 PM
 

Paul Nielsen said:

Thanks Alex,

This is one of the few cases where I support "responsible denormlization." Before I started writing and training, I used to specializee in writing custom inventory systems and I agree 100% with your post.

Adam, I'm not sure that returning hundreds of thousands of rows to the client to calculate balance on hand every time an inventory system or user needs to know how many widgets are in bin 123 is a very sellable idea. In some plants this operation happens thousands of times a day.

-Paul

January 28, 2009 10:35 PM
 

Alexander Kuznetsov said:

Paul,

Thank you for your encouragement!

AK

January 29, 2009 1:19 PM
 

Alexander Kuznetsov said:

I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you

March 8, 2009 8:34 PM
 

Vince Bowdren said:

Is there any easy way to insert new records on past dates? Either before the first date, or between two existing dates?

March 23, 2009 6:47 AM
 

Alexander Kuznetsov said:

Vince,

On 2008 you can use MERGE. On 2005 and before, you have to insert a row at the end, then use an UPDATE to move it. I am planning to write it up.

March 23, 2009 9:27 AM
 

Alexander Kuznetsov said:

Suppose that you need to enforce the following business rule: contracts cannot be changed after you have

May 3, 2009 10:20 PM
 

Alexander Kuznetsov said:

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table

May 4, 2009 11:35 PM
 

Alireza Haghshenas said:

Hi.

I disagree that a good solution should be implemented on the client. This type of information is not local to the few tens of records you may send to a client (paging), but depends on all the information prior to that page.

Any good solution should enable us to create a view in server that can be queried in a fraction of a second and is cached by the system, so much less time is spent on concurrent queries.

Your solution seems like a real promise, a great improvement over anything else I've seen so far...

I'll try it in action

July 19, 2010 6:12 AM
 

Alireza Haghshenas said:

Well, there is a fast solution on the client that supports paging, but requires more code and is more complex to maintain:

send the page to the client, accompanied by the grand total of all row prior to the sent page, the client updates the running total for each row, starting from the provided value.

By the way, I still prefer to have the running totals on the server.

Thanks for the great solution :)

July 19, 2010 6:17 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement