Tuesday, March 11, 2008

Generate Sequential GUIDs for SQL Server 2005 in C#

Why generate sequential GUID in C#?

Originally, uniqueidentifier (GUID) column in SQL Server was not supposed to be sequential. But in my case, having sequential GUID is quite useful.My application needs to know, what record was inserted first.

That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].

Final solution

Now we're ready to write C# code, that would sequentially increment any given GUID.I also made it more convenient to increment GUID, by using "++" operator.Here's how it's used:private void Test(){ SequentialGuid = new SequentialGuid(Guid.Empty); SequentialGuid++;}

9 comments:

In my case I run code on several different machines, and thus is not able to use a single shared SequentialGuid object. Is there a way to call the constructor with a value depending on e.g. DateTime.Now?

In other words, I need a method to call anywhere from any server and still get i sequential guid larger than those generated before.

I see at least two possible ways to solve "Generate Sequential GUID across multiple machines":

Solution #1:Create web service that all your machines would call. This service would generate unique sequential GUIDs for all other machines.

Solution #2:Put DateTime.Now into appropriate bytes of GUID (your idea). Select these GUID bytes in such a way that GUIDs will be sorted by using these datetime bytes first and would sort by other bytes later.You may put machine number into last (the least significant) byte of GUID, so machine number would not affect sorting, but it still would ensure that GUIDs generated on different machines are unique.Use "middle" GUID bytes to put sequential number in case if you need to generate more than one GUID during the same millisecond.

Could you please elaborate on your discussion of the most and least significant bits. Both on how you determined the significance of the bits and then what the implications are of changing the highest vs the lowest.

Thanks for your answer Dennis, yes that makes sense I just wasn't sure which way it went if the sort was based off the most significant or least significant bytes there until your comment cleared it up for me.