newid() vs newsequentialid() What are the differences/pros and cons?

sql-server-2005 primary-key identity
7wp picture 7wp · Oct 19, 2009 · Viewed 22.5k times · Source

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

ChadT picture ChadT · Oct 19, 2009

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:

enter image description here

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

Related questions

How to add composite primary key to table

create table d(id numeric(1), code varchar(2)) After I create the above table how can I add a composite primary key on both fields and also a foreign key?

Read More
Entity Framework and SQL Server View

For several reasons that I don't have the liberty to talk about, we are defining a view on our Sql Server 2005 database like so: CREATE VIEW [dbo].[MeterProvingStatisticsPoint] AS SELECT CAST(0 AS BIGINT) AS 'RowNumber', CAST(0 AS BIGINT) AS 'ProverTicketId', …

Read More
Best way to change clustered index (PK) in SQL 2005

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_…

Read More