sql server 2005 - newid() vs newsequentialid() What are the differences/pros and cons?

Question

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.

Answer 1

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.

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

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