sql-server-2005 - newid() 与 newsequentialid() 有什么区别/优缺点？

Question

在所有主键都是 GUID 的数据库中，使用 newid() 与 newsequentialid() 作为“默认值或绑定(bind)”有什么区别/含义和/或优缺点。

我所知道的唯一区别是 newid() 创建一个新的随机 GUID，而不是 newsequentialid() 基于表中的最后一个以递增方式创建一个新的 GUID。

Answer 1

当您在 DB 的行中执行插入时，它将按相对于表中其他 PK 的顺序插入。使用普通的 guid，这可能是表中的任何位置。 newsequentialid() 将始终添加到表的末尾。
因此插入的性能得到了提高。
This site解释了两种不同方法之间的差异和基准。
更新 - 引用的博客文章已被移动。该链接现在指的是 web.archive.org 链接。这是关键要点:

Most striking is the number of writes required by the NEWID system function. This, coupled with the average page density of 69%, is evidence of the page splitting caused by the random distribution of inserts at the leaf level. As soon as a page fills up, it needs to be split into 2 pages of 50% each for the insert to complete. Not only has page splitting resulted in poor page density, it has fragmented the data pages quite badly (there is a 99% probability that the next data page is not next to the current one). In our tests the most likely place for a free page required for the page split is at the end of the table irrespective of where the row is being inserted. Therefore to read the rows in order the scan needs to keep jumping back and forth between widely distributed split pages, hence the appalling fragmentation.

——斯特凡·德尔马科