Is there a .NET equalent to SQL Servers newsequentialid()

Question

Hi

We use Guid's 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 Guid's 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 GUID's should be unique etc.

Answer 1

The key problem is knowing what the last value was in a .NET application. SQL Server keeps track of this for you. You will need to hold the last value yourself and use the Guid constructor with a byte array containing the next value. Of course, on a distributed application this probably isn't going to help and you may have to use the randomised Guids. (Not that I see anything wrong with this.)

http://msdn.microsoft.com/en-us/library/90ck37x3.aspx

Answer 2

Perhaps a simple way to determine the order in which rows have been added would be to add an IDENTITY column to the table, avoiding the need to keep your GUIDS in order and hence avoiding the performance hit of maintaining a clustered index on the GUID column.

I can't help but wonder how keeping these rows in order helps you when debugging. Could you expand that a bit?

Answer 3

Unfortunatley, no there isn't a .NET equivalent to newsequentialid(). You could continue using a Comb. I actually have a C# implementation of a Comb somewhere...I'll see if I can dig it up.

Answer 4

By insisting on having your rows "in order" you are subverting the important principle that tables are conceptually an unordered set. Order can be imposed of course, via "ORDER BY" arguments and indexes, but by trying to force your database to perpetually maintain rows in a state that matches your (incorrect) mental model you are going to continue to struggle.

Once more I appeal for you to please explain how keeping rows in a physical order helps you with debugging. It might be that there is an easier way to achieve your debugging goals.

Answer 5

It should be possible to create a sequential GUID in c# or vb.net using an API call to UuidCreateSequential. The API deceleration (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 6

I've been lead to believe that random Guids can be beneficial to performance in some use cases. Apparently inserting to random pages can avoid contention that would otherwise occur in the end page when multiple people are trying to insert at the same time.

John's PInvoke suggestions is probably the closest to SQL's version but the UUidCreateSequential docs state that you shouldn't use it to identify an object that it's strictly local to the machine generating the Guid.

I'd be measuring the actual use case performance hit with realistic data in realistic quantities before I investigated sequential Guid generation any further.

Answer 7

Here is the C# code to generate a COMB GUID.

byte[] guidArray = System.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 = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks));

            // 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 System.Guid(guidArray);

Answer 8

About the selected answer. The docs says... The generated Guid will not give you uniqueId between computers if they don't have ehternet access.

If you must know the guid when inserting, couldn't you let Sql-server return a block of sequential guids that you assign to your data before you insert them?

declare @ids table(id uniqueidentifier default NEWSEQUENTIALID(), dummy char(1))

declare @c int set @c = 0; while (@c < 100) begin insert into @ids (dummy) values ('a'); set @c += 1; end

select id from @ids

//Daniel