O'Reilly logo
  • Dustin Waybright thinks this is interesting:

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.

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

If the ID is a primary key, a new record will be added to the table relative to the PK's position to the table. With NEWID(), this could be anywhere on the table as the GUID is not sequential. With NEWSEQUENTIALID(), it will always add the record at the end of the table which generates a rightmost leaf in the index and is more efficient. http://www.verynoisy.com/sql-indexing-dummies/ has an explanation of indexing nodes.