newid()vs newsequentialid()有什么优缺点? [英] newid() vs newsequentialid() What are the differences/pros and cons?

问题描述

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

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

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

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.

推荐答案

在数据库的一行中执行插入时，将相对于表中的其他PK依次插入该DB.如果使用普通向导，则可以在表中的任何位置. newsequentialid()将始终添加到表格的末尾.

When you perform an insert in a row the DB, it will be inserted in order relative to the other PK's 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.

更新-所引用的博客文章已被移动.现在，该链接指向一个web.archive.org链接.这是关键要点:

Update - the blog post referenced has been moved. The link now refers to an web.archive.org link. Here is the key takeaway:

最醒目的是NEWID系统功能所需的写入次数.这与69％的平均页面密度相结合，证明了页面级别的插入是由于在叶级别插入的随机分布而导致的页面分裂.页面填满后，需要将其分成2页，每页50％，以完成插入.页面拆分不仅导致糟糕的页面密度，而且还严重破坏了数据页面的碎片(下一个数据页面不在当前页面的概率为99％).在我们的测试中，页面插入所需的空闲页面最有可能放置在表的末尾，而不管插入行的位置.因此，要读取行以使扫描需要在分布广泛的拆分页面之间来回跳动，因此会产生令人震惊的碎片.

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.

-Stefan Delmarco

--Stefan Delmarco

这篇关于newid()vs newsequentialid()有什么优缺点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！