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: 
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)
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
