Notes on Primary Key Identity and uniqueidentifier

RecommendationArticle:

Http://tech.ddvip.com/2008-10/122517676785777_4.html

Test Using guid as the primary key of the database

Http://www.cnblogs.com/chiname/articles/227584.html

Use uniqueidentifier or identity to generate a primary key?

Problem

I am designing a table and decide to create an automatically generated primary key value instead of creating my own mode or using a natural key. I know that SQL Server provides a globally unique identifier (guid) and an identifier for creating these values. What are the advantages and disadvantages of these methods??

Expert answers

Indeed, you can use many methods to automatically generate primary key values for your table. The most common method is to use the identity field attribute or specify a unique identifier data type with the default newid () or newsequentialid () function. In addition,In SQL Server replication, globally unique identifiers are often used in the unique identifiers of merged replication or transaction replication with update subscriptions..

What is the difference between newid () and newsequentialid ()??Newid () randomly generates a unique value guaranteed by the unique ID number of the server Nic and the unique number of the CPU block. In contrast, newsequentialid () generates these values in sequence rather than randomly.

As you can see,The first table uses newid () to generate random values, while the second table uses newsequentialid () to generate ordered values. Because the identity method is not used to generate an integer, the generated globally unique Identifier value does not seem easy to use at work. This is another area of attention.. The SQL Server saves the last generated id value in the memory. After using the insert Statement of scope_identity (), @ identity or check_ident (depending on the scope you want, find the id value again. This is similar to obtaining the last Globally Unique Identifier value.If you use a globally unique identifier, you must create your own mechanism to obtain the last inserted value (for example, find the globally unique identifier before insertion or use the SQL Server 2005 output clause).

Storage Effect

Based on the output results, you can see that the newid () test table is fragmented because its fragmentation ratio is 98.%. In addition, you can see that these rows are scattered on the 490 page. This is because the random attribute of the primary key is generated, resulting in page splitting. In contrast, the identity and newsequentialid () test tables have only a very small proportion of fragments because they automatically generate keys in a specific order. The results prevent page splitting, and the newid () method is used to split pages. Although you can use disk fragmentationProgramThe newid () Table is processed, but the random attributes generated by the primary key will still cause page splitting and Fragmentation due to future insert operations on the table. However, page splitting can be minimized by specifying the appropriate fill factor.

This problem is also taken into consideration. Because the unique identifier data type consumes 16 bytes of data, any definition that uses a globally unique identifier as a clustered index in a table will be affected by the size of the non-clustered index, because the leaf level of these non-clustered indexes contains the clustered index key as the indicator. Therefore, if an identity is defined as an integer or bigint data type, the size of any non-clustered index may be terminated because it is large.

Obviously, using identity to automatically generate key values is moreAdvantages:

L The value generated by identity is configurable and easy to read and use at work.

L fewer database pages can be used to meet query requests.

L compared with the newid () method, its concerns about page splitting (related to the previous issue) can be eliminated.

L minimize the database size.

L there are system functions to obtain the final generated identity value (for example, scope_identity)

Some system functions, such as Min () and max (), cannot be used in the Unique Identifier Field.

Test Using guid as the primary key of the database

It can be seen that adding a time-type or Int-type index to the table with guid as the primary key can compensate for the performance loss caused by using guid as the primary key.

3This is done to simulate the actual use of the situation, of course, this is very one-sided, because in the ordinary use process, often use paging, calculate the total number of data, the purpose is to check whether the calculation efficiency will be affected if no additional index is added to the table. It seems that using guid as the primary key requires additional indexing to ensure that the calculation like Count does not consume too much time, however, using the int type does not seem to consume too much time, so we can see that,If you use the varchar field type as the primary key table, a non-clustered index is required in the table..