Is there a .NET equivalent to SQL Server's newsequentialid()

Question

We use GUIDs for primary key, which you know is clustered by default.

When inserting a new row into a table it is inserted at a random page in the table (because GUIDs are random). This has a measurable performance impact because the DB will split data pages all the time (fragmentation). But the main reason I what a sequential GUID is because I want new rows to be inserted as the last row in the table... which will help when debugging.

I could make a clustered index on CreateDate, but our DB is auto-generated and in development, we need to do something extra to facilitate this. Also, CreateDate is not a good candidate for a clustered index.

Back in the day, I used Jimmy Nielsons COMB's, but I was wondering if there is something in the .NET framework for this. In SQL 2005 Microsoft introduced newsequentialid() as an alternative to newid(), so I was hoping that they made a .NET equivalent because we generate the ID in the code.

PS: Please don't start discussing if this is right or wrong, because GUIDs should be unique etc.

Answer 1

It should be possible to create a sequential GUID in c# or vb.net using an API call to UuidCreateSequential. The API declaration (C#) below has been taken from Pinvoke.net where you can also find a full example of how to call the function.

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

The MSDN article related to the UuidCreateSequential function can be found here which includes the prerequisites for use.

Answer 2

Update 2018: Also check my other answer

This is how NHibernate generate sequantial IDs:

NHibernate.Id.GuidCombGenerator

/// <summary> /// Generate a new <see cref="Guid"/> using the comb algorithm. /// </summary> private Guid GenerateComb() {     byte[] guidArray = Guid.NewGuid().ToByteArray();      DateTime baseDate = new DateTime(1900, 1, 1);     DateTime now = DateTime.Now;      // Get the days and milliseconds which will be used to build the byte string      TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);     TimeSpan msecs = now.TimeOfDay;      // Convert to a byte array      // Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333      byte[] daysArray = BitConverter.GetBytes(days.Days);     byte[] msecsArray = BitConverter.GetBytes((long) (msecs.TotalMilliseconds / 3.333333));      // Reverse the bytes to match SQL Servers ordering      Array.Reverse(daysArray);     Array.Reverse(msecsArray);      // Copy the bytes into the guid      Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2);     Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4);      return new Guid(guidArray); }