When you are using Record.SystemId in Business Central then you are guaranteed that the ID is unique and random, right?

Recently I have been involved in discussions, where we would need to send records from Business Central to an external system that would keep all the sent records, which could be accessed externally through an identifier.
“There is no better time to use SystemId”, I thought. This blog entry will go in to details about how SystemId is generated, and why we have ultimately decided to not use it in this case.

What is SystemId

SystemId was introduced with BC15 and adaption and support in the platform has continously grown. If you look at the documentation you will see that essentially the field contains a GUID that can either be specified directly on the record by using some code along the lines of

Record.SystemId := CreateGuid();
Record.Insert(true, true);

or it can be auto-generated by the platform.

In addition to it being filled out automatically then the platform guarantees that the ID is unique for that table. It does this with a unique index on the table in the SQL database. This allows developers to also use Record.GetBySystemId(Guid); to get a record. (This is really handy btw, if you are passing references around to records that comes from tables with different primary keys, e.g. Sales Header and Sales Invoice Header).

The reason we wanted to use SystemId

We wanted to use the SystemId because the values are auto-generated by the platform. The table that in this particular case is relevant is not a new table so by using SystemId, we would not have to fill in GUIDs for all previous records in an upgrade codeunit to support our new feature.

Using GUIDs are generally a nice solution for providing a unique value for a given record. If you do not know, GUIDs (or UUIDs as they are also called) are a 128-bit value, converted to ASCII which results in a string with 32 characters and four hypens, like ad45495e-4e94-4407-b5d4-a706eca0af57.

There is no centralized system for keeping track of these generated ids so theoretically, you can hit a collision, but the possibility of that is so close to zero that it is generally accepted that they are globally unique.

Why we cannot use SystemId

“All of this sounds great! Why can’t you use it then?”, might you be thinking right now. I had the same thought initially. There are two things that are a deal breaker for us:

  1. Notice how I wrote they are unique for that table? That is true! But in the way that it is implemented there is less of a guarantee that they are unique across tables and tenants. For the first iteration it would not be too important that they really are globally unique. We have however already discussed features that would require it to be globally unique.

  2. The auto-generated GUIDs are actually guessable! “How?”, you might ask. Let’s have a look.

How is the SystemId generated

To generate the GUID for SystemId BC uses a default value on the SQL tables that it’s inserting. This means that if you don’t specify a value then SQL will generate one for the platform and eventually you, the developer.

If you have an on premise version of BC, you can check the SQL schema on the MSSQL server. If you want to see it yourself execute the following SQL statement on the server:

SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name = '$systemId'
AND    object_id = object_id('dbo.tablename')

Change the dbo.tablename to a table you want to inspect. Be sure to select the table from the base application such that the table actually contains the field.

What you will see is that it makes use of a built-in SQL server function called NEWSEQUENTIALID(). If you know a bit about MSSQL servers, you know that there is also a function called NEWID() which also generates a GUID. So what is the difference and why does it matter?

NEWID() vs. NEWSEQUENTIALID()

Plenty of blog posts have been written on the difference between if you dive in to the MSSQL community just a little bit. But you are already here, so let me bring you up to speed.

The documentation for NEWSEQUENTIALID specifies the following:

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function.

This means that the value generated by the server is tied to the time on the server. The value will still be somewhat random but if you obtain one value, you can also calculate what the next value is.

This can be easily seen if you execute the following SQL query

DROP TABLE IF EXISTS test;

CREATE TABLE test (
    id int primary key,
    newid uniqueidentifier default newid(),
    newseqid uniqueidentifier default newsequentialid()
);

DECLARE @Counter int;
SET @Counter = 0;

WHILE (@Counter <= 10)
BEGIN
    INSERT INTO test (id) VALUES (@Counter);
    SET @Counter = @Counter + 1;
END;

SELECT * FROM test;

When I run on it, I get the following output:

Comparison of NEWID and NEWSEQUENTIALID in MS SQL

As can be seen the values from NEWID() are not following the same sequence as NEWSEQUENTIALID().

Conclusion

As could probably be guessed from the function name, indeed the IDs generated by NEWSEQUENTIALID() are sequential. Which also means that they’re guessable. In our case this is a complete no-go since we don’t want a user with the knowledge of one value to be able to guess other possible values.

In general, I still strongly recommend the use of SystemId. In this specific case it was just a good fit. Luckily, the work around for us is pretty easy. We will just use System.CreateGuid(); in AL and save it in a separate field, we create ourselves.