RECID numbering in Dynamics 365 FO

In general, there should be no problems with the record number (RECID) allocation in D365FO and you will probably not run into issues, if you are starting a brand-new installation of the product.

However, I was handed an AX 2012 upgraded database and ran into problems with records that could not be inserted due to already existing records. I had a lot of these issues in AX 2012, so my suspicion was immediately drawn towards the record number sequence. 

In AX 2012 we would query the SYSTEMSEQUENCES table of the related database, but in D365FO things are a little different and since I was unable to find this documented anywhere, I decided to write this post.

Microsoft has switched from using a dedicated database table to using the standard SQL sequences functionality, which all in all looks very much like the old way, except that the assignment of record numbers are no longer done via stored procedures rather than a ‘NEXT VALUE FOR’ function on the table field.

Consider a new table called ‘Table1’ with just one added field beside the system added ones:

Right clicking the RECID field and choosing Modify, will show the default properties for the SQL field:

Here we can see the actual “NEXT VALUE FOR” call and that the table ID is used to generate the sequence name.

All the sequences are held in the SYS.SEQUENCES object:

So, if we want to find the sequence for any given D365FO table, we just need to know its name and use this statement:

SELECT * from TABLEIDTABLE

JOIN sys.sequences ON sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)

WHERE TABLEIDTABLE.NAME = 'Table1'

These are the default settings for a newly created table telling us that the first record we create will have the RECID = 5637144576:

Also notice the cache information which is important if you decide to change the sequence, which of course you should only do if you have any problems with the system reusing old numbers.

If for some reason records in the table exist with a greater record number than 5637144576 at some point, we would hit an already used number: 

we would get an error like:

To change the sequence first we need to know what number should be the next one, and we do that by executing this SQL statement:

select max(RECID) FROM [AXDB].[dbo].[TABLE1]

The result of this SELECT will be the highest record number in the table (5637144577 in the sample above) and we should pick a next value a bit above that, let us say 5637150000.

Use the ALTER SEQUENCE statement to change the next value:

ALTER SEQUENCE SEQ_22096

       MINVALUE 5637150000

       RESTART WITH 5637150000

As written earlier the sequence name is generated into a string like 'SEQ_[TableId]'

The RESTART WITH option should make sure the cache is flushed in the SQL server, but I have experienced that stopping and starting the service related to D365FO might be required:

• World Wide Web Publishing Service (or IIS Express on development boxes)

After that the next record number drawn should be 5637150000:

For further documentation on the sequence functionality please follow this link: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sequences-transact-sql?view=sql-server-2016