How SQL Server uses an incrementing sort GUID for the master key

Scene:

Product table Data volume large you want to use a GUID to make the table's primary key, and to establish a clustered index on this field. Because the GUID is generated randomly, the resulting value size is indeterminate, and the number of builds each time may be large or small. This will affect the efficiency of the insertion

1. The difference betweennewsequentialid and NEWID ()

Both Newsequentialid () and NEWID () can produce a GUID of type uniqueidentifier. The GUID produced by NEWID () is unordered, random.
While Newsequentialid () is a new feature of SQL SERVER2005, Newsequentialid is hardware-based (to some extent) generated GUIDs that increment in hexadecimal intervals.

An official explanation?
A new Newsequentialid function has been added to SQL SERVER 2005, and the explanation for MSDN is:
Creates a GUID on the specified computer that is greater than any GUID that was previously generated by the function.
Newsequentialid () cannot be referenced in the query and cannot execute statements like Select Newsequentialid ();
It can only be used with the DEFAULT constraint on the uniqueidentifier Type table column.
The GUID that is generated by the Newsequentialid () is unique only on that particular computer when the computer does not have a network card, and the GUID that is generated is unique globally only if the computer has a network card.

Difference?
The biggest benefit of the 1.newsequentialid function compared to the NEWID function is that if you are indexing on a uniqueidentifier field, the new value generated by using NEWID is not fixed, so the new value causes the change of the index B + tree to be random. While the new values generated by NEWSEQUENTIALID are regular, the changes to the index B + tree are regular to reduce page contention on leaf-level indexes. Regular and irregular performance improvements can result.

2.UNIQUEIDENTIFIER key (Primary key) is a very convenient thing, in the data merge and other operations have irreplaceable advantages
However, because of the dispersion of the common GUID, if the primary key plus the clustered index (Clustered index) results in a much less efficient insertion of the record

The reason is simple:
1. Cache Hit Rate problem
You can see this article I wrote earlier: Understanding Cache http://database.ctocio.com.cn/tips/219/7791219.shtml
When the GUID that is generated is regular, you find several intermediate nodes of the leaf node that need to be manipulated, which may have been cached in previous accesses.
In this way, the system does not need to read a large number of index data pages with very low cache hit ratios, which saves memory while improving search speed.
2. Impact of continuous and discontinuous disk I/O operations on performance
As we all know, the bottleneck of many business logic now is the speed of the hard disk. and the drive speed of the space is still small. The following rules on hard disk read and write operations are useful for optimizing the aspects of hard disk I/O.
Keep in mind the following rule of thumb: The standard Wide Ultra SCSI-3 HDD provides 75 discrete (random) I/O operations and 150 consecutive I/O operations per second for Windows and SQL Server. This drive has a nominal transmission rate of approximately MB/s. Keep in mind that it is more likely to limit the transfer rate of the database server to 75/150 I/O per second, rather than to a max MB/second.
The read/write heads and associated disk picker arms need to be moved to find and operate on the location of the hard disk platters required by SQL Server and Windows. If the location of the hard disk where the data resides is not contiguous, the hard drive takes much longer to move the disk picker and read/write heads to all the required hard disk platters locations. If the required data is all in a contiguous physical sector on the hard disk platter, in contrast, the disk picker and read/write heads can perform the required disk I/O operations with minimal movement. The time spent in continuous and discontinuous situations varies greatly, with each discontinuous data lookup taking approximately 50 milliseconds, while continuous data lookups take approximately 2-3 milliseconds. Note that these values are roughly estimated, depending on the distribution of discontinuous data on the disk, the rotational speed (RPM) of the hard disk platters, and other physical properties of the hard disk. One important thing to keep in mind is that continuous I/O benefits SQL Server performance. It has been mentioned that standard hard drives support 75 discontinuous I/Os per second and 150 consecutive I/Os per second. It is also important to remember that the time to read or write 8KB is almost the same as when you read or write to KB. Within the range of 8 KB to KB, the time spent on a single disk I/O transfer operation is primarily the time of the disk picker and the read/write head movement. Therefore, mathematically speaking, when it is necessary to transfer more than a KB of SQL data, it is beneficial to perform as many kilobytes as possible, since the 8 KB transfer is basically as fast as the 8 KB transfer and 8 times times the SQL Server data per transfer. Keep in mind that Read-ahead Manager performs disk operations with a KB byte slice (also known as a SQL Server extents). Log Manager also performs sequential writes with a large amount of I/O transfer. The main things to keep in mind are to take full advantage of Read-ahead Manager and separate the SQL Server log files from other non-contiguous access files to effectively improve SQL Server performance.

How SQL Server uses an incrementing sort GUID for the master key