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

Question

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?

Answer 1

Sage have not designed the database to be written directly to (and they don't support development where this is the case).

Sage have an SDK that enables you to write to the database via objects and this simplifies the process of writing routines to import Exchange Rates or discounts etc.

To get the SDK requires being an accredited Sage developer, which in turn involves money.