I'm wondering if anyone knows how SAP B1 (SAP Business One) generates the unique primary keys it uses in various tables. Examples of what I am talking about would include OCRD.DocEntry and OCPR.CntctCode. These integer columns that get "automatically" incremented.
Typical approaches for doing this include identity columns (e.g., SQL Server), sequences (e.g., Oracle), or manual sequence tables holding a Nextval which is programmatically incremented. As best I can tell, B1 is not using any of these techniques for these columns. So how is it handling them?
The particular instance I'm looking at is using an SQL Server database.
Yes, I'm well aware of the fact that there is no "need" for me to know know about the inner workings, shouldn't be mucking around in the DB, etc. It's just bothering me that I don't know how they are doing it! If anyone can explain, I'd be grateful.
SAPB1 generates new unique numbers using the ONNM table. When a document is added the following takes place.
Running an SQL SELECT statement with an update lock returns the current row while simultaneously locking that row until the end of the transaction. You are guaranteed that no other user can change that row between when you select it and when the transaction ends.
You can use SQL Profiler to watch the statements executed when you perform actions in SAP B1. Here is the line that gets the next number to use in a Quotation. Quotations are ObjectType 23.
SELECT T0.* FROM [dbo].[ONNM] T0 WITH (UPDLOCK) WHERE T0.[ObjectCode] = '23'