Insight Into Record IDs
17Sep
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]
[Link (Requires partnersource login]: https://mbs.microsoft.com/partnersource/documentation/whitepapers/msdyax40_writing_upgrade_scripts_whitepaper.htm?printpage=false]
CREATE PROCEDURE initFromSMMQuotationTable @DATAAREAID NVARCHAR(3AS DECLARE @NEXTVAL BIGINT, @ROWCOUNT BIGINTSELECT ……,RECID = IDENTITY(BIGINT,0,1) AS QUOTATIONID –Assign an IDENTITY column with a starting value of 0 incremented by 1INTO #TEMPFROM DEL_SMMQUOTATIONTABLE WHERE QUOTATIONSTATUS = 0 –SMMQuotationStatus::InProcessSELECT @NEXTVAL=NEXTVAL –Retrieve the next value for RECID for this table (by TABID)FROM SYSTEMSEQUENCESWITH(UPDLOCK, HOLDLOCK) WHERE ID = -1 AND TABID = 1967INSERT INTO SALESQUOTATIONTABLE(column-list)SELECT ……,RECID = QUOTATIONID+@NEXTVAL –When we insert into the permanent table, we add the temporary table‟s IDENTITY column to the next value retrieved from SYSTEMSEQUENCESFROM #TEMPSELECT @ROWCOUNT = COUNT(*) FROM #TEMPUPDATE SYSTEMSEQUENCES –We update SYSTEMSEQUENCES to reflect the number of rows that we have added to this tableSET NEXTVAL=NEXTVAL + @ROWCOUNTWHERE ID = -1 AND TABID = 1967GO
========
Handling RecIds in SQL Server
01AprI have already written one article on handling RecIds in AX (click here to read it).
There I have mentioned a method to handle RecIds using SQL. I am writing this article after actually implementing it .
The need arose because we had some recalculations and data inserts required for one of our clients (almost more than 2 million records).
Last time when we had run the script to perform this task in AX, it took us 4 days to complete the whole task. So we decided to try and perform these tasks using SQL Scripts for better performance.
I am not writing that script here but I will take an example and explain you how we can handle RecIds through SQL Server.
Let us assume that we need to backup current customer master to a new table. For this purpose I created a new table named SGXCustTable with following fields:
Now run the following SQL Script and you will see that the data is getting inserted properly and quickly. That is the power of SQL. You will find comments inline that will help you understand each step in SQL query.
- AccountNo
- Name
- CreatedTime
- ModifiedTime
- CreatedDate
- ModifiedDate
- CreatedDateTime (Assuming CustTable has this property enabled)
- ModifiedDateTime(Assuming CustTable has this property enabled)
- CreatedTransactionId
- ModifiedTransactionId
- CreatedBy
- ModifiedBy
- DataAreaId
- RecVersion
- RecId
DECLARE @NEXTVAL BIGINT, @ROWCOUNT BIGINT, @NEXTVALTRANS BIGINTDROP TABLE #TEMP–First insert all the required data into a temporary table with RecId as an identity field–Note: *CreatedTime and ModifiedTime are stored as integers hence convert them to corresponding integer– *RecId field is made as an identity field that will be used later for calculating Record Ids– *GETDATE() when used with date field automatically converts to date and with datetime field automatically gets datetimeSELECT [ACCOUNTNUM], [NAME], [MODIFIEDTIME]=(DATEPART(hour, GETDATE()) * 3600)+(DATEPART(MINUTE, GETDATE()) * 60)+DATEPART(SECOND, GETDATE()),[CREATEDTIME]=(DATEPART(hour, GETDATE()) * 3600)+(DATEPART(MINUTE, GETDATE()) * 60)+DATEPART(SECOND, GETDATE()),[CREATEDDATE]=GETDATE(),[MODIFIEDDATE]=GETDATE(), [CREATEDDATETIME],[MODIFIEDDATETIME],[CREATEDTRANSACTIONID]=0,[MODIFIEDTRANSACTIONID]=0,[CREATEDBY]=‘SUMIT’,[MODIFIEDBY]=‘SUMIT’, [DATAAREAID], [RECVERSION],[RECID] = IDENTITY(BIGINT,0,1)INTO #TEMPFROM CUSTTABLE WHERE CUSTTABLE.DATAAREAID = ‘CEU’–The Next RecId value is stored in SystemSequences Table–Get the Next RecId and store it in a variable–Note the TABID will be the table id into which records are being insertedSELECT @NEXTVAL=NEXTVALFROM SYSTEMSEQUENCESWITH(UPDLOCK, HOLDLOCK) WHERE ID = -1 AND TABID = 50051–Note that this is required for ModifiedTransactionId and CreatedTransactionId–The sequence for this is stored per company with TABID as 0 (Independent of tables) and ID = -2–SELECT @NEXTVALTRANS=NEXTVALFROM SYSTEMSEQUENCESWITH(UPDLOCK, HOLDLOCK) WHERE TABID = 0 AND ID = -2–Now read all the records from temporary table and insert into the required table–Note that RecId, CreatedTransactionId and ModifiedTransactionId are made using the NextVal–fetched from SystemSequences and Identity columnINSERT INTO SGXCUSTTABLE (ACCOUNTNO, NAME, MODIFIEDTIME, CREATEDTIME, CREATEDDATE, MODIFIEDDATE,CREATEDDATETIME, MODIFIEDDATETIME, CREATEDTRANSACTIONID, MODIFIEDTRANSACTIONID,CREATEDBY, MODIFIEDBY, DATAAREAID, RECVERSION, RECID)SELECT [ACCOUNTNUM], [NAME], [MODIFIEDTIME],[CREATEDTIME],[CREATEDDATE],[MODIFIEDDATE],[CREATEDDATETIME],[MODIFIEDDATETIME],[CREATEDTRANSACTIONID]=@NEXTVALTRANS+[RECID],[MODIFIEDTRANSACTIONID]=@NEXTVALTRANS+[RECID],[CREATEDBY],[MODIFIEDBY], [DATAAREAID], [RECVERSION],[RECID] = @NEXTVAL + [RECID]FROM #TEMP–Select the number of records insertedSELECT @ROWCOUNT = COUNT(*) FROM #TEMP–Update the used number sequences back to SystemSequences tableUPDATE SYSTEMSEQUENCESSET NEXTVAL=NEXTVAL + @ROWCOUNTWHERE ID = -1 AND TABID = 50051UPDATE SYSTEMSEQUENCESSET NEXTVAL=NEXTVAL + @ROWCOUNTWHERE TABID = 0 AND ID = -2 AND DATAAREAID = ‘CEU’SELECT * FROM SGXCUSTTABLE