Generated Sequential GUIDs are sometimes not sequential

Question

I have a C# application that generates a Sequential GUID for each row that I insert into a table. I expect the inserted GUIDs to be sequential, but sometimes they break sequence (in chunks).

Example:

These GUIDs are shown in the order that they are inserted.

Why are these 'sequential' GUIDs being created with such a large break of sequence?

Code used to generate sequential GUIDs:

class NativeMethods
{
    [DllImport("rpcrt4.dll", SetLastError = true)]
    public static extern int UuidCreateSequential(out Guid guid);
}

public static Guid CreateSequentialGuid()
    {
        const int RPC_S_OK = 0;

        Guid guid;
        int result = NativeMethods.UuidCreateSequential(out guid);
        if (result == RPC_S_OK)
            return guid;
        else
            return Guid.NewGuid(); //<--In debugging, this statement never runs.
    }

Code used in a loop to insert new GUIDs and information into table:

Guid mySequentialGUID = CreateSequentialGuid();
string[] row = { item1,
                 item2,
                 item3,  
                 sourceText,
                 encoding.ToString(),
                 mySequentialGUID.ToString()
             };
var listViewItem = new ListViewItem(row);
myListView.Items.Add(listViewItem);

Edit: Please see this question to understand Sequential GUIDs:

A GUID exists to be a unique number. No more, no less. While there are different algorithms one can use for generating them, you should not rely on particular properties of particular implementations. Tread a `GUID` as a GUID, no more. If you need an incrementing number then *get a number and increment it*, rather than using a GUID, which is *only* a representation of a way of generating *unique* numbers. — Servy, Jan 27 '17 at 17:14
I've never heard of a sequential GUID, but that seems completely wrong. Did you make this up? A GUID by its very nature is unique and you can't control how it's generated. If you *know* what the next GUID that will be generated is (because it's next in sequence from your last one), then it's not unique, because someone else can do the same thing starting with the same start value. — rory.ap, Jan 27 '17 at 17:16
@rory.ap I did not make up a sequential GUID. There is even a built in method to create sequential GUIDS in Transact-SQL, called NewSequentialID(). https://msdn.microsoft.com/en-us/library/ms189786.aspx — Kyle Williamson, Jan 27 '17 at 17:19
@rory.ap I've added a link to a SO question, who's answer explains why someone would use a sequential GUID. The answer uses the same C# code as I do. — Kyle Williamson, Jan 27 '17 at 17:24
Is it possible that two GUIDs were created around the same time in two different transactions and one transcation was rolled back? In addition to what has already been pointed out, there's really no reliable way in a transactional environment to always have sequential GUIDs. — Peter Ritchie, Jan 27 '17 at 17:24
Why does it matter? As long as they're still in order, it will still work and the indexes won't have to be reordered. It's not as if they're going to run out of values :) — stuartd, Jan 27 '17 at 17:25
It seems [sequential guids](http://stackoverflow.com/questions/5585307/sequential-guids) use the system time so i could see there being jumps when the clock updates (about every 15ms). — Mike Zboray, Jan 27 '17 at 17:25
From a database POV, I can't see why you'd ever use a GUID as a primary identifier (for a clustered index, which is where it might cause problems). This seems like a fix for a broken idea. — spender, Jan 27 '17 at 17:35
@spender I have linked a SO question to explain why you may use a GUID as a primary identifier. — Kyle Williamson, Jan 27 '17 at 18:27
You mention that the Guid.NewGuid() line never executes during debugging. Do you know if it executes when not debugging? Do you observe your issue when debugging? You can see how it's possible that the native method sometimes returns <> 0 and then you might mess up the sequential Guid by generating a new one from managed code. — Guillaume CR, Jan 27 '17 at 19:46

score 5 · Accepted Answer · answered Jan 27 '17 at 22:26

I think the problem is that you assume that "sequential" means "increment by one". Although your example shows that this happens sometimes there's no actual guarantee in the documentation that it will. We could argue the definition of "sequence" or that maybe this function is named poorly or even wrong but at the end of the day, according to the documentation it appears to be working 100% exactly as defined:

Creates a GUID that is greater than any GUID previously generated by this function

For people that have a problem with the general concept of a "sequential GUID" use the definition of "sequence" that's about "following a logical order" and also try and remember the Melissa virus. This was mildly document by this line:

For security reasons, UuidCreate was modified so that it no longer uses a machine's MAC address to generate UUIDs. UuidCreateSequential was introduced to allow creation of UUIDs using the MAC address of a machine's Ethernet card.

So the "sequence" is basing the GUID off of the MAC address.

Back to the OP's question, my best guess for why you have GUIDs that don't increment by one each time is the simple fact that you are probably not the only one calling this function. This is a core Windows function and there's bound to be other components, programs, services, etc. that use it.

This answer is correct, but I would just like to add that if the OP is looking to use the result to insert rows in SQL Server, she should probably be aware that UuidCreateSequential is not fully sequential with respect to SQL Server’s sort order, and need to perform some byte suffling: https://blogs.msdn.microsoft.com/dbrowne/2012/07/03/how-to-generate-sequential-guids-for-sql-server-in-net/ — martin, Oct 18 '17 at 07:07

score 2 · Answer 2 · answered Jan 27 '17 at 18:30

The NewSequentialId() starts over when the system restarts. See here.

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 is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Thank you for your answer. I realize this can occur after Windows restarts. My `CreateSequentialGuid()` method is being called in one quick `for` loop and my machine is not being restarted. — Kyle Williamson, Jan 27 '17 at 18:52

Generated Sequential GUIDs are sometimes not sequential

2 Answers2

Linked