C# 是否有 .NET 等同于 SQL Servers newssequentialid()

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

提问by Thomas Jespersen

We use Guid's for primary key, which you know is clustered by default.

我们使用 Guid 作为主键，您知道默认情况下它是集群的。

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.

向表中插入新行时，它会插入到表中的随机页面（因为 Guid 是随机的）。这会对性能产生可衡量的影响，因为 DB 会一直拆分数据页（碎片）。但是我使用顺序 Guid 的主要原因是因为我希望将新行作为表中的最后一行插入......这将有助于调试。

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.

我可以在 CreateDate 上创建一个聚集索引，但我们的数据库是自动生成的，并且在开发中，我们需要做一些额外的事情来促进这一点。CreateDate 也不是聚集索引的良好候选者。

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.

回到我使用Jimmy Nielsons COMB's 的那一天，但我想知道 .NET 框架中是否有用于此的东西。在 SQL 2005 中，Microsoft 引入了 newsequentialid() 作为 newid() 的替代方案，所以我希望他们制作了一个等效的 .NET，因为我们在代码中生成了 ID。

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

PS：请不要开始讨论这是对还是错，因为 GUID 应该是唯一的等等。

采纳答案by John

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.netwhere you can also find a full example of how to call the function.

应该可以使用对 UuidCreateSequential 的 API 调用在 c# 或 vb.net 中创建顺序 GUID。下面的 API 声明 (C#) 取自Pinvoke.net，您还可以在其中找到有关如何调用该函数的完整示例。

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

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

可以在此处找到与 UuidCreateSequential 函数相关的 MSDN 文章，其中包括使用的先决条件。

回答by BlackWasp

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.)

关键问题是知道 .NET 应用程序中的最后一个值是什么。SQL Server 会为您跟踪此情况。您需要自己保存最后一个值，并使用带有包含下一个值的字节数组的 Guid 构造函数。当然，在分布式应用程序上，这可能不会有帮助，您可能必须使用随机化的 Guid。（并不是说我认为这有什么问题。）

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

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

回答by Ed Guiness

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.

也许确定行添加顺序的一种简单方法是向表中添加一个 IDENTITY 列，从而避免需要保持 GUIDS 有序，从而避免在 GUID 列上维护聚集索引对性能造成影响。

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

我不禁想知道如何在调试时保持这些行的顺序对您有帮助。你能扩大一点吗？

回答by Scott Dorman

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.

不幸的是，没有 .NET 相当于newsequentialid(). 您可以继续使用梳子。我实际上在某处有一个梳子的 C# 实现......我会看看我是否可以挖掘它。

回答by Jason Stangroome

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.

我一直相信随机 Guids 在某些用例中可以提高性能。显然，插入到随机页面可以避免当多人同时尝试插入时会在结束页面中发生的争用。

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.

John 的 PInvoke 建议可能是最接近 SQL 版本的，但是 UUidCreateSequential 文档指出您不应该使用它来识别一个对象，它严格属于生成 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.

在我进一步研究顺序 Guid 生成之前，我会用真实数量的真实数据来衡量实际用例性能的影响。

回答by Donny V.

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

这是生成 COMB GUID 的 C# 代码。

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

回答by Daniel

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

关于所选答案。文档说...如果计算机没有以太网访问权限，则生成的 Guid 不会为您提供计算机之间的 uniqueId。

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?

如果您在插入时必须知道 guid，难道您不能让 Sql-server 在插入之前返回您分配给数据的顺序 guid 块吗？

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

回答by Gian Marco Gherardi

Update 2018:Also check my other answer

2018 年更新：另请查看我的其他答案

This is how NHibernate generate sequantial IDs:

这就是 NHibernate 生成序列 ID 的方式：

NHibernate.Id.GuidCombGenerator

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

回答by Ian Boyd

It's important to note that the UUIDs generated by UuidCreateSequentialwill not be sequential when ordered by SQL Server.

需要注意的是，UuidCreateSequential生成的 UUID在 SQL Server 排序时不会是顺序的。

• SQL Server follows the RFCwhen it comes to sorting UUIDs
• the RFC got it wrong
• UuidCreateSequentialdid it right
• but UuidCreateSequentialcreates something different from what SQL Server expects

• SQL Server 在对 UUID 进行排序时遵循RFC
• RFC 弄错了
• UuidCreateSequential做对了
• 但UuidCreateSequential创建了与 SQL Server 预期不同的东西

Background

背景

The Type 1 UUIDs created by UuidCreateSequential don't sort in SQL Server.

UuidCreateSequential 创建的类型 1 UUID 不在 SQL Server 中排序。

SQL Server's NewSequentialIDuses UuidCreateSequential, with some byte shuffling applied. From the Books Online:

SQL Server 的NewSequentialID使用UuidCreateSequential，并应用了一些字节改组。来自在线书籍：

NEWSEQUENTIALID (Transact-SQL)

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

NEWSEQUENTIALID (Transact-SQL)

NEWSEQUENTIALID 是 Windows UuidCreateSequential 函数的包装器，应用了一些字节改组

which then references an MSDN blog post:

然后引用了 MSDN 博客文章：

How to Generate 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);
}

如何在 .NET 中为 SQL Server 生成顺序 GUID（存档）

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 since 1582-10-15 00:00:00(October 15, 1592, the date of Gregorian reform to the Christian calendar). Ticks is the number of 100 ns intervals.

这一切都始于自1582-10-15 00:00:00（1592 年 10 月 15 日，即公历改革到基督教日历的日期）以来的刻度数。Ticks 是 100 ns 间隔的数量。

For example:

例如：

• 12/6/2017 4:09:39 PM UTC
• = 137,318,693,794,503,714 ticks
• = 0x01E7DA9FDCA45C22ticks

• 12/6/2017 下午 4:09:39 UTC
• = 137,318,693,794,503,714 滴答
• =0x01E7DA9FDCA45C22滴答声

The RFC says that we should split this value into three chunks:

RFC 说我们应该把这个值分成三个块：

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

• UInt32 低（4 字节）
• Uint16 mid（2 字节）
• UInt32 hi（2 字节）

So we split it up:

所以我们把它分开：

0x01E7DA9FDCA45C22

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

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

然后RFC说这三个整数应该按以下顺序写出：

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

• 低：0xDCA45C22
• 中：0xDA9F
• 高：0x01E7

If you follow the RFC, these values must be written in big-endian(aka "network byte order"):

如果您遵循 RFC，则这些值必须以big-endian（又名“网络字节顺序”）编写：

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

This was a bad design, because you cannot take the first 8 bytes of the UUID and treat them either as a big-endian UInt64, nor as a little-endian UInt64. It's a totally dumb encoding.

这是一个糟糕的设计，因为您不能将 UUID 的前 8 个字节视为大端 UInt64 或小端 UInt64。这是一个完全愚蠢的编码。

UuidCreateSequential gets it right

UuidCreateSequential 做对了

Microsoft followed all the same rules so far:

到目前为止，微软遵循了所有相同的规则：

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

• 低：0xDCA45C22
• 中：0xDA9F
• 高：0x1E7

But they write it out in Intel little-endianorder:

但是他们以 Intel little-endian顺序写出来：

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

If you look at that, you've just written out a little-endian Int64:

如果你看一下，你刚刚写出了一个 little-endian Int64：

225CA4DC9FDAE701

Meaning:

意义：

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

• 如果你想提取时间戳
• 或按时间戳排序

it's trivial; just treat the first 8 bytes as a UInt64.

这是微不足道的；只需将前 8 个字节视为 UInt64。

With the RFC, you have no choice but to perform all kinds of bit fiddling. Even on big-endian machines, you can't treat the 64-bit timestamp as a 64-bit timestamp.

使用 RFC，您别无选择，只能执行各种比特摆弄。即使在大端机器上，也不能将 64 位时间戳视为 64 位时间戳。

How to reverse it

如何扭转它

Given a little-endian guid from UuidCreateSequential:

给定来自 的 little-endian guid UuidCreateSequential：

DCA45C22-DA9F-11E7-DDDD-FFFFFFFFFFFF

with the raw bytes of:

原始字节为：

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

This decodes into:

这解码为：

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

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

• 低：0xDCA45C22
• 中：0xDA9F
• 高：0x1E7
• 版本：1（类型1）

We can write this back out in RFC big-endian order:

我们可以按照 RFC big-endian 顺序将其写回：

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

回答by Gian Marco Gherardi

You can use the tiny NewId library for this.

您可以为此使用微小的 NewId 库。

Install it via NuGet:

通过 NuGet 安装它：

Install-Package NewId

And use it like this:

并像这样使用它：

Guid myNewSequentialGuid =  NewId.NextGuid();

See Project Page on GitHub

请参阅GitHub 上的项目页面