SQL Server Identity vs uniqueidentifier

The most common method to automatically generate a primary key is:

1. the identity attribute treats the original seed value as its first parameter and the increment value as its second parameter value.

2. specify your field as the unique identifier type and use the default constraint of the newid () or newsequentialid () function. If the field is specified as the unique identifier type, the default constraint must be used to assign a globally unique identifier to this field newid ()

Differences between identity and uniqueidentifier:

A. the Globally Unique Identifier value does not seem easy to use at work. SQL Server saves the last generated id value in the memory, so that you can use scope_identity (), @ identity or check_ident (depending on

After the insert statement for the range you requested), find the id value again. if you use a globally unique identifier, you must create your own mechanism to obtain the last inserted value (for example, relocate the globally unique identifier before insertion ).

Or use SQL Server 2005 output clause ).

B. Use the following statements to check the physical storage of data in the database:

Selectobject_name ([object_id]) astablename, avg_fragmentation_in_percent, fragment_count, page_count
Fromsys. dm_db_index_physical_stats (db_id (), null)
Orderbytablename
Go

This statement shows that the newid () test table is fragmented because the proportion of fragments is 98%. 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. 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 the disk fragment program to process the newid () table, the random attribute 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.

By observing the newsequentialid () test table, we can easily find that it produces fewer pages than the newid () method, but still produces more pages than the identity method. Why? This is because the unique identifier data type consumes 16 bytes of disk space, rather than using only four bytes of integer data type as the identity method. Considering that the SQL Server Page is usually limited to 8 K or about 8060 bytes (because of a row overflow mechanism in SQL Server 2005 to solve this problem, this is a problem), the consequence is:

The newsequentialid () method generates more pages instead of the Identity method.

By viewing the used database tablespace, we can see that the tables using the identity method use the minimum disk space:

　　execsp_spaceusedIDENTITY_TEST1
　　GO
　　execsp_spaceusedIDENTITY_TEST2
　　GO
　　execsp_spaceusedIDENTITY_TEST3
　　GO
　　execsp_spaceusedNEWID_TEST
　　GO
　　execsp_spaceusedNEWSEQUENTIALID_TEST
　　GO

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 more advantageous than using globally unique identifiers:

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

2. Fewer database pages can be used to meet query requests.

 

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

4. Minimize the database scale.

5. There are system functions to obtain the final generated identity value (for example, scope_identity)

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