Sorting of guid columns and Cluster Index in SQL Server

Today, I investigated a defect in the project and found that SQL Server sorts the guid column andClusterSpecial processing of index. Two knowledge points are involved. One is how SQL Server sorts guid columns.AlgorithmImplementation. The second is the difference between cluster index and noncluster index. First, let's talk about how SQL Server sorts the guid columns. DetailsArticleHere:

Http://blogs.msdn.com/ B /sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx

The simple algorithm is that the guid format is {000000000-0000-0000-00000000}, and the comparison is in bytes. The ratio starts from 10-15 bits, and then is 8-9 bits, 6-7 bits, 4-5 bits, and 0-3 bits. Therefore, pay special attention to the difference between this method and the method in. net. The difference between cluster index and noncluster index. The first major difference is that the storage method is different. The storage of Row Records after the Cluster Index is created is based on the physical location priority. That is to say, SQL Server tries its best to store row record information in adjacent locations on the same storage page (using the append method ). In this case, the records are stored in a continuous manner, and the database will be quickly searched and read, because there is no need to jump between storage pages. But it also brings about a problem that to ensure the continuity of the row record, when inserting and updating the row record, the location of other records needs to be moved, which leads to performance problems during insertion. The benefit of noncluster index is that it is stored logically. The index information records the offset of Row Records on the storage page and page, and all the index information is stored as a tree. This makes it easy to insert data. You only need to find the free page and record the page number and offset. At the same time, it is relatively slow to locate the index on the tree and then read the records on the specified page. Therefore, you need to think twice about how to set the table index. The problem I want to talk about here is that when the guid column is used as the cluster index, no matter whether the guid value is changed or not, its row sequence number will change as long as the update operation is performed. That is to say, using the same SELECT statement to query the same record results in different record order. Therefore, this issue should be noted when using cluster index.