Search code examples
sqldatabaset-sqldatabase-designsage-erp

Sage 200 - Counter table instead of Identity columns - strange behaviour

I've recently been tasked with writing a Sage import tool that imports Quantity Price Break Discounts.

The Sage 200 tables in question are:

  • StockItem - Main Product Table
  • StockItemDiscount - Main Discount Table
  • StockItemQtyDiscBreak - Discount Qty Price Breaks

I wont bore you with schema information as it's not relevant to my question, suffice to say - the primary key in all 3 tables is a BigInt without identity set (sigh), 1 StockItem can have many Discounts and 1 Discount can have many Qty Discount Breaks.

Now then, to create an import routine i first had to analyse what Sage 200 did on SQL if you created Discount and Breaks manually in sage (using SQL Profiler). As i say, Sage 200 does not make use of Identity columns, instead it uses a counter table.

Inserting a new row into StockItemDiscount did the following:

UPDATE [Counter] SET [NextValue] = [NextValue] + 10 WHERE [CounterID] = 1

It then selects the new ID:

SELECT NextValue FROM Counter WHERE CounterID = 1

It then inserts the new row using the new value it just selected from the counter:

INSERT INTO StockItemDiscount (StockItemDiscountID, /.../) VALUES (@NewID, /.../)

My question is this: Why on earth is Sage doing it this way? what could possibly be the reasoning behind it? (Specifically the +10 THEN reading the value)

All the tables share the same counter too, so 5 rows in 1 table would results in a gap in the id's of another table - i'm just really at a loss as to why they do it like this?

The reason i ask: After inserting a row into StockItemDiscount i then need to delete any related rows in StockItemQtyDiscBreak & insert replacements - however, using SQL profiler i cant see incrementing of the counter table unless i insert 5 or more discounts (the 6th causes it to hit the counter table again, it's almost as if the Sage UI is reserving those 10 ID's using them for a variety of inserts then reserving an additional 10 as it needs them - this just seems very very odd to me?

Solution

  • Theory #1:

    They are inserting on N0, and reserving N1-N9 for future edits?

    Theory #2:

    They are using N0 for a parent record, and using N1-N9 as child record ID's? Although you said all 5 tables use the same counter and method, so this doesn't seem likely.

    Are there any rows where N1-N9 is being used?

    Theory #1a & #2a:

    They intended to implement one of these, and changed their mind, or they previously implemented this, and went away from this model, but never cleaned up their code / methods.