newid() 与 newsequentialid() 有什么区别/优缺点？
在所有主键都是 GUID 的数据库中，使用 newid() 与 newsequentialid() 作为“默认值或绑定”有什么区别/含义和/或优缺点。
我知道的唯一区别是 newid() 创建一个新的随机 GUID，而不是 newsequentialid() 基于表中最后一个以递增方式创建新的 GUID。
In a database where all of your primary keys are GUIDs, what are the differences/implications and/or pros and cons using newid() versus newsequentialid() as the "default value or binding".
The only difference that I know of is that newid() creates a new random GUID as opposed to newsequentialid() creates a new GUID based on the last one that is in the table in an incremented fashion.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
当您在数据库中执行插入操作时，它将按照相对于表中其他 PK 的顺序插入。使用正常的指南，这可能位于表中的任何位置。 newsequentialid() 将始终添加到表的末尾。
因此刀片的性能得到提高。
本网站 解释了两种不同方法之间的差异和基准。
更新 - 引用的博客文章已被移动。该链接现在引用 web.archive.org 链接。这是关键要点：
——斯特凡·德尔马科
When you perform an insert in a row the DB, it will be inserted in order relative to the other PKs in the table. With a normal guid, this could be anywhere in the table. A newsequentialid() will always be added to the end of the table.
So the performance of inserts is improved.
This site explains the differences and benchmarks between the two different methods.
Update - the blog post referenced has been moved. The link now refers to an web.archive.org link. Here is the key takeaway:
--Stefan Delmarco
关于顺序密钥（如身份、序列和 NEWSEQUENTIALID）与非顺序密钥（如 NEWID 或自定义随机密钥生成器）的使用，有几个方面需要考虑。
从顺序键开始，所有行都进入索引的右端。当页面已满时，SQL Server 会分配一个新页面并填充它。这会减少索引中的碎片，从而有利于读取性能。此外，当单个会话加载数据并且数据驻留在单个驱动器或少量驱动器上时，插入速度会更快。
然而，对于具有多个轴的高端存储子系统，情况可能有所不同。当从多个会话加载数据时，最终会出现针对索引叶级链接列表的最右边页面的页面锁存器争用（锁存器是用于同步对数据库页面的访问的对象）。该瓶颈阻碍了存储子系统的全部吞吐量的使用。
请注意，如果您决定使用顺序键并且使用数字键，则始终可以从类型中的最低值开始以使用整个范围。例如，在 INT 类型中，您可以从 -2,147,483,648 开始，而不是从 1 开始。
考虑非顺序密钥，例如使用 NEWID 或自定义解决方案生成的随机密钥。当尝试将一行强制放入已满的页面时，SQL Server 会执行经典的页面拆分 — 它分配一个新页面并将一半的行从原始页面移动到新页面。页面分割是有成本的，而且会导致索引碎片。索引碎片会对读取性能产生负面影响。但是，就插入性能而言，如果存储子系统包含许多轴，并且您从多个会话加载数据，则尽管存在分割，但随机顺序实际上可能比顺序更好。
那是因为索引的右端没有热点，并且您使用存储子系统的
可用吞吐量更好。展示此策略的基准测试的一个很好的例子可以在 Thomas Kejser 的博客中找到，网址为 http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generate-scalable-keys/。
来源：
查询 Microsoft®
SQL Server® 2012
考试 70-461
训练套件
Regarding the use of sequential keys (as with identity, sequence, and NEWSEQUENTIALID) vs. nonsequential ones (as with NEWID or a custom randomized key generator), there are several aspects to consider.
Starting with sequential keys, all rows go into the right end of the index. When a page is full, SQL Server allocates a new page and fills it. This results in less fragmentation in the index, which is beneficial for read performance. Also, insertions can be faster when a single session is loading the data, and the data resides on a single drive or a small number of drives.
However, with high-end storage subsystems that have many spindles, the situation can be different. When loading data from multiple sessions, you will end up with page latch contention (latches are objects used to synchronize access to database pages) against the rightmost pages of the index leaf level’s linked list. This bottleneck prevents use of the full throughput of the storage subsystem.
Note that if you decide to use sequential keys and you’re using numeric ones, you can always start with the lowest value in the type to use the entire range. For example, instead of starting with 1 in an INT type, you could start with -2,147,483,648.
Consider nonsequential keys, such as random ones generated with NEWID or with a custom solution. When trying to force a row into an already full page, SQL Server performs a classic page split—it allocates a new page and moves half the rows from the original page to the new one. A page split has a cost, plus it results in index fragmentation. Index fragmentation can have a negative impact on the performance of reads. However, in terms of insert performance, if the storage subsystem contains many spindles and you’re loading data from multiple sessions, the random order can actually be better than sequential despite the splits.
That’s because there’s no hot spot at the right end of the index, and you use the storage subsystem’s
available throughput better. A good example for a benchmark demonstrating this strategy can be found in a blog by Thomas Kejser at http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/.
Source:
Querying Microsoft®
SQL Server® 2012
Exam 70-461
Training Kit
根据我的理解，当 SQL 实例启动时，NEWSEQUENTIALID GUID 被初始化为随机值。然后，在其操作的生命周期中，GUID 在中央 GUID 上递增，而不是通过查看为表生成的最后一个 GUID。
From my understanding, when the SQL instance fires up the NEWSEQUENTIALID GUID is initialised to a random value. Then for the life of its operation GUIDs are incremented on the central GUID, NOT by looking at the last GUID generated for the table.
据我所知，
NEWID()以随机顺序生成GUID，NEWSEQUENTIALID()以顺序生成GUID命令。NEWSEQUENTIALID()只能在表的默认子句中使用。As I know,
NEWID()generates theGUIDin random order andNEWSEQUENTIALID()generates theGUIDin sequential order.NEWSEQUENTIALID()can be used ONLY in default clause of a table.