Is .NET equal to SQL servers newsequentialid ()

Question

We use Guid for the primary key, which, as you know, is clustered by default.

When a new row is inserted into a table, it is inserted on a random page in the table (since Guid is random). This has a measurable impact on performance because the database breaks the data pages all the time (fragmentation). But the main reason that the sequential Guid is because I want new rows to be inserted as the last row in the table ... which will help with debugging.

I could create a clustered index in CreateDate, but our database is automatically generated and in development, we need to do something extra to facilitate this. Also, CreateDate is not a good candidate for a clustered index.

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

PS: Please do not start discussing whether this is correct or not, because the GUID must be unique, etc.

Answer 1

It should be possible to create a consistent GUID in C # or vb.net using the API call for UuidCreateSequential. The API declaration (C #) below was taken from Pinvoke.net , where you can also find a complete example of a function call.

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

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

Answer 2

Update 2018: Also check out my other answer

So NHibernate generates sequential identifiers:

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); } 

Answer 3

Perhaps a simple way to determine the order in which the rows were added would be to add an IDENTITY column to the table, avoiding the need to keep your GUIDs in order, and therefore avoiding performance gains while maintaining the clustered index on the GUID.

I can't help but wonder how to keep these lines in order when you are debugging. Could you expand it a bit?

Answer 4

It is important to note that UUIDs created using UuidCreateSequential will not be ordered by SQL Server.

• SQL Server follows RFC when it comes to sorting UUIDs
• RFC was wrong.
• UuidCreateSequential did it right.
• but UuidCreateSequential creates something different from what SQL Server expects

Background

Type 1 UUIDs created by UuidCreateSequential are not sorted in SQL Server.

SQL Server NewSequentialID uses UuidCreateSequential , with some byte-shuffling applied. From online books:

NEWSEQUENTIALID (Transact-SQL)

NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function, with some byte shuffling applied

which then links to the MSDN blog post:

How to create sequential GUIDs for SQL Server in .NET ( archive )

 public static Guid NewSequentialId() { Guid guid; UuidCreateSequential(out guid); var s = guid.ToByteArray(); var t = new byte[16]; t[3] = s[0]; t[2] = s[1]; t[1] = s[2]; t[0] = s[3]; t[5] = s[4]; t[4] = s[5]; t[7] = s[6]; t[6] = s[7]; t[8] = s[8]; t[9] = s[9]; t[10] = s[10]; t[11] = s[11]; t[12] = s[12]; t[13] = s[13]; t[14] = s[14]; t[15] = s[15]; return new Guid(t); } 

It all starts with the number of ticks from 1582-10-15 00:00:00 (October 15, 1592, the date of the Gregorian reform to the Christian calendar). Ticks are the number of intervals of 100 ns.

For example:

• 06/12/2017 4:09:39 UTC
• = 137 318 693 794 503 714 ticks
• = 0x01E7DA9FDCA45C22 ticks

The RFC says that we should divide this value into three parts:

• UInt32 low (4 bytes)
• Uint16 mid (2 bytes)
• UInt32 hi (2 bytes)

So we divided it:

 0x01E7DA9FDCA45C22 | Hi | Mid | Low | |--------|--------|------------| | 0x01E7 | 0xDA9F | 0xDCA45C22 | 

And then the RFC says that these three integers should be written in the following order:

• Low: 0xDCA45C22
• Mid: 0xDA9F
• High: 0x01E7

If you follow the RFC, these values ​​should be written in big-endian format (otherwise called "network byte order"):

 DC A4 5C 22 DA 9F x1 E7 xx xx xx xx xx xx xx xx 

It was a bad design because you cannot take the first 8 bytes of UUIDs and treat them both at the large end of UInt64 and at low-intensity UInt64. This is an absolutely dumb encoding.

UuidCreateSequential gets it right

Microsoft followed all the same rules:

• Low: 0xDCA45C22
• Mid: 0xDA9F
• High: 0x1E7

But they write it in Intel's little-endian order:

 22 5C A4 DC 9F DA E7 x1 xx xx xx xx xx xx xx xx 

If you look at this, you just wrote out little-endian Int64 :

 225CA4DC9FDAE701 

Value:

• if you want to extract the timestamp
• or sort by timestamp

it is trivial; just treat the first 8 bytes as UInt64.

In RFC, you have no choice but to execute all kinds of bit-bits. Even on big end machines, you cannot treat a 64-bit timestamp as a 64-bit timestamp.

How to cancel it

Given the small endian guid from UuidCreateSequential :

 DCA45C22-DA9F-11E7-DDDD-FFFFFFFFFFFF 

with raw bytes:

 22 5C A4 DC 9F DA E7 11 DD DD FF FF FF FF FF FF 

This is decoded to:

 Low Mid Version High -------- ---- ------- ---- ----------------- DCA45C22-DA9F-1 1E7 -DDDD-FFFFFFFFFFFF 

• Low: 0xDCA45C22
• Mid: 0xDA9F
• High: 0x1E7
• Version: 1 (type 1)

We can write this back in RFC order:

 DC A4 5C 22 DA 9F 11 E7 DD DD FF FF FF FF FF FF 

Short version

  | Swap | Swap | Swap | Copy as-is Start index | 0 1 2 3 | 4 5 | 6 7 | End index | 3 2 1 0 | 5 4 | 7 6 | ---------------|-------------|-------|-------|------------------------ Little-endian: | 22 5C A4 DC | 9F DA | E7 11 | DD DD FF FF FF FF FF FF Big-endian: | DC A4 5C 22 | DA 9F | 11 E7 | DD DD FF FF FF FF FF FF 

Answer 5

Unfortunatley, there is no .NET equivalent for newsequentialid() . You can continue to use Comb. I actually have a C # implementation for the comb somewhere ... I'll see if I can dig it out.

Answer 6

Here is the C # code for creating the GUID COMB.

 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 7

For people who specifically use the Entity Framework , you can store the stored procedure on the server to generate a new sequential identifier and return the identifier. You can then use this sequential identifier to populate another table . I think this should work.

Answer 8

A key issue is knowing the last value in a .NET application. SQL Server keeps track of this for you. You will need to save the last value yourself and use the Guid constructor with a byte array containing the following value. Of course, this probably won't help in a distributed application, and you might have to use randomized guides. (Not that I don't see anything wrong with that.)

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

Answer 9

I am fortunate that random Guides can be useful for performance in some use cases. Apparently, pasting onto random pages can avoid the competition that would otherwise occur on the final page when several people try to insert at the same time.

John PInvoke's suggestions are probably the closest to the SQL version, but the UUidCreateSequential docs indicate that you should not use it to identify an object that is strictly local to the machine generating the Guid.

I would evaluate the actual effectiveness of using use cases with realistic data in realistic amounts before I study the sequential construction of Guid.

Answer 10

About the selected answer. The docs say ... Created by Guid will not give you a uniqueId between computers if they do not have Internet access.

If you need to know the manual on insertion, could you let the Sql server return a block of consecutive pointers that you assign to your data before inserting 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 

Answer 11

You can use the tiny NewId library for this.

Install it through NuGet:

 Install-Package NewId 

And use it like this:

 Guid myNewSequentialGuid = NewId.NextGuid(); 

See the project page on GitHub