Insight Into Record IDs

Record IDs are unique IDs. In AX 4.0 Record id is unique for a table, a significant shift in 4.0 when compared to 3.0 where record ids were unique across the application. This allows AX to store more data and support enormous number of records.
 
In AX 4.0 SystemSequences table stores record ids details for all the tables in AX. The generation of Record ids is handled by class SystemSequence.
 
Record ids are generated at the time of saving the record. This is what system does. based on the table id, ID = -1 and name =’SEQNO’ system gets a block of record ids and caches them and stores in the client. The block is of size 250 (in 3.0 we could change the block size, but in 4.0 MS doesnt allow anybody to change the block size).
 
Here is a sample code that shows how we can get next record id in AX 4.0
 
static void getNextRecIdAX40(Args _args)
{
    //Table that stores record ids details for tables
    SystemSequences systemSequences;
 
    //Class that handles Record id generation
    SystemSequence  systemSequence = new SystemSequence();
    ;
 
    select firstonly systemSequences where systemSequences.tabId == tableNum(CustTable);
 
    systemSequence.suspendRecIds(systemSequences.tabId);
    info(strFmt(‘Next record id: %1’, systemSequence.reserveValues(systemSequences.minVal, systemSequences.tabId)));
    systemSequence.removeRecIdSuspension(systemSequences.tabId);
}
 
Here is a sample code that shows how we can get next record id in AX 3.0
 
static void getNaxtRecIdAX30(Args _args)
{
    SystemSequence  systemSequence;
    ;
 
    systemSequence = new SystemSequence();

    systemSequence.flushCache();
    systemSequence.setCacheSize(30)

 

    info(strFmt(‘Buffer size: %1’, systemSequence.getCacheSize()));
    info(strFmt(‘Next record id: %1’, systemSequence.nextVal();));
}
Below is a sample script to generate record ids while you are inserting data from SQL server scripts.
 
[Source for the code: How to Write Data Upgrade Scripts for Microsoft Dynamics AX 4.0 white paper from MS]

CREATE PROCEDURE initFromSMMQuotationTable @DATAAREAID NVARCHAR(3

AS DECLARE @NEXTVAL BIGINT, @ROWCOUNT BIGINT

 

SELECT ……,

RECID = IDENTITY(BIGINT,0,1) AS QUOTATIONID Assign an IDENTITY column with a starting value of 0 incremented by 1

INTO #TEMP

FROM DEL_SMMQUOTATIONTABLE WHERE QUOTATIONSTATUS = 0 SMMQuotationStatus::InProcess

 

SELECT @NEXTVAL=NEXTVAL Retrieve the next value for RECID for this table (by TABID)

FROM SYSTEMSEQUENCES

WITH(UPDLOCK, HOLDLOCK) WHERE ID = -1 AND TABID = 1967

 

INSERT INTO SALESQUOTATIONTABLE

(column-list)

SELECT ……,

RECID = QUOTATIONID+@NEXTVAL When we insert into the permanent table, we add the temporary tables IDENTITY column to the next value retrieved from SYSTEMSEQUENCES

FROM #TEMP

 

SELECT @ROWCOUNT = COUNT(*) FROM #TEMP

 

UPDATE SYSTEMSEQUENCES             We update SYSTEMSEQUENCES to reflect the number of rows that we have added to this table

SET NEXTVAL=NEXTVAL + @ROWCOUNT

WHERE ID = -1 AND TABID = 1967

GO

2 thoughts on “Insight Into Record IDs

  1. Hi.  I\’m new to AX and find your log invaluable.  Thank you.What purpose does RecID now serve that wouldn\’t have been better implemented in 4.0 as an identity column?

  2. Hi,
    RecId is not only unique for a table in AX 4.0 but what MS has does is that the management of RecIds is in the hands of AX and not SQL server. This they have done for better performance and also re-usability. That is if any previous record has been deleted and later you see that you want to reuse the RecIds you can do so. System has a way of cleaning up the unused system sequences…
    Thats why MS has not made it an identity column…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s