Dynamics 365 Business Central: use sequential GUIDs when possible.

I think that everyone of you know what a GUID is.

A GUID (Globally Unique Identifier) is a 128-bit number used to uniquely identify information in computer systems. A GUID is represented as a 32-character hexadecimal string, typically formatted with hyphens like this: 550e8400-e29b-41d4-a716-446655440000. The 128 bits are divided into five groups that follow a specific structure defined by standards like RFC 4122.

GUIDs are generated algorithmically rather than assigned sequentially by a database. Different algorithms can generate GUIDs in various ways, but the key principle is that each generated GUID is extremely unlikely to collide with another GUID generated elsewhere in the world. The mathematical probability of two randomly generated GUIDs being identical is astronomically small.

One of the advantages of GUIDs is that they can be generated on the client side before data is sent to the database, unlike auto-incrementing integers which require the database to assign values.

While GUIDs provide uniqueness and can be generated anywhere, they consume more storage than integers (16 bytes vs 4-8 bytes). Traditional GUID can also cause performance issues due to their random nature, which is why sequential GUIDs became important for SQL performance.

In AL language, the method Guid.CreateGuid() creates new unique non-sequential GUIDs, which are not optimized for SQL indexing (create fragmentations).

In this example, The External ID field uses a random GUID, not ideal for indexing:

But the biggest problem occurs when you have a GUID as a primary key for records:

This is usually something that you should try to avoid. When you have a GUID as a primary key of a table, this GUID is used as the clustered index of the table (and records are ordered on disk as the clustered index). In this case, with every INSERT you would be changing the layout of the data potentially having to move many data pages.

When data pages on disk are not in physical order, it can increase disk operations, by having to resort to many smaller contiguous chunks being read instead of fewer big ones. Index fragmentation can also have an effect on which strategy the engine decides for performing the query.

Remember that the cost of writing GUID primary keys in a database containing 1,000,000 records is 10x the cost of writing INT primary keys. This cost goes up exponentially when the table contains more rows.

Why Sequential GUIDs?

Sequential GUIDs significantly reduces index fragmentation, leading to smoother, faster database operations. They can provide a 20-25% performance increase, better index usage, and less disk usage compared to standard GUIDs.

Sequential GUIDs are important in SQL for several key reasons:

• Performance Benefits: sequential GUIDs significantly improve database performance compared to random GUIDs. When you insert random GUIDs as primary keys, they scatter data throughout the index, causing excessive page splits and fragmenting the index. Sequential GUIDs, on the other hand, insert in order, keeping related data clustered together and minimizing index fragmentation. This leads to faster queries and better overall database performance.
• Index Efficiency: with sequential GUIDs, the database can append new records to the end of the clustered index without reorganizing existing pages. Random GUIDs force the database to insert values at random locations, which requires constant rebalancing of the B-tree index structure. This makes sequential GUIDs much more efficient for high-volume insert scenarios.
• Reduced Page Splits: page splits occur when the database needs to move data around to make room for new insertions. Sequential GUIDs minimize page splits because new values always go to the end of the index. Random GUIDs cause frequent page splits throughout the index, which is a costly operation that degrades performance.
• Better Cache Locality: sequential GUIDs keep related data on the same or nearby disk pages, improving cache locality. When the database fetches a page, it can access multiple related records efficiently. Random GUIDs scatter data across many pages, requiring more disk I/O operations.
• Storage Efficiency: while sequential GUIDs don’t reduce the storage space required (both are 16 bytes), they do reduce the overhead caused by index fragmentation and page splits, effectively making better use of allocated storage.

To optimize performance when you need to have GUID for identifying records, starting from Dynamics 365 Business Central 2025 Wave 2 release (version 27) you can use the new CreateSequentialGuid method, that creates a new sequential unique GUID (using the same logic as the standard SystemId column:

This is absolutely much better for indexing (alias performance). Keep this in mind…

The main trade-off is that sequential GUIDs are predictable, which could be a security concern if you expose them publicly on some scenarios, whereas random GUIDs provide better obfuscation of data patterns.

Let’s close this post with some numbers. In version 1 of an application, I had a table storing event signals coming from a cloud service. This table had 3 main fields:

• ID: GUID of the event signal (random GUID)
• Payload: JSON details of the event
• EventDate: date and time when the event occurs.

In version 2 of the same application, we changed the primary key generation (ID field) moving from a random GUID to a sequential GUID. These are the results when inserting 10.000.000 of records and then updating 2.000.000 of that records:

Sequential GUIDs in this case was around 42% better on insert and around 31% better on updating records compared to using random GUIDs. Not a small gain…

Original Post https://demiliani.com/2025/11/21/dynamics-365-business-central-use-sequential-guids-when-possible/