[Reprinted] Application of uniqueidentifier data type in system design

1. What is uniqueidentifier?

Uniqqueidentifier is a globally unique identifier.

 

Ii. How do I assign values to columns of the uniqueidentifier data type?

1. Use the newid () function to implement

2. directly convert the constant of the string to the format XXXXXXXX-XXXX-xxxxxxxxxxxx.

Example: 6f9619ff-8b86-d011-b42d-00c04fc964ff is a valid uniqueidentifier data

3. assign a 32-bit sixteen-bit data directly.

Example: 0xffffffff00000000ffffff00000000

3. How is the uniqueidentifier data actually stored in the database?

The actual data stored in uniqueidentifier is a binary value of 16 bytes,

Uniqueidentifier can be converted to the actual string type and binary data type.

 

How does the newid () function generate a unique uniqueidentifier value?

The newid () function is used to generate new uniqueidentifier data from the unique numbers on their NICs that identify numbers and CPU clocks, this data is the same as the guid, and each computer can generate a globally unique value.

In this way, a unique identifier is generated between multiple computers and multiple networks.

 

5 main advantages of using the uniqueidentifier Data Type

The main advantage of uniqueidentifier data type is that the global uniqueness of values can be guaranteed when the newid function is used to generate values.

A unique record that identifies a single row is more effective for multiple databases (especially databases with multiple machines and multiple network segments) than identity.

Secondly, when identity is used, the automatically generated values cannot be modified, while the uniqueidentifier data type can be modified at any time.

6. disadvantages of using the uniqueidentifier Data Type

1. The value of the uniqueidentifier type is unordered.

When relevant data information is normally displayed, the returned information is unordered.

When the data identified by identity is displayed, it is displayed by default in the order of adding records.

In this way, for the information set whose uniqueidentifier is the primary key, you still need a field that identifies the sorting by default.

2. For the uniqueidentifier field, the actual information of the data is 16 bytes, which is much larger than the identity, which reduces the efficiency of storage space and query.

7. In the design of the system database, we should consider how to use uniqueidentifier, identity, and identifiable record attributes (information with actual meanings) as primary keys.

System design with attribute as the primary key

In the process of system design

A single message contains attributes that can represent uniqueness (generally, no more than three attributes) and such attributes are mandatory fields. Generally, no changes are made during the record life cycle, and more than 50 such systems are used in the table.

It is best to use attribute as the primary key.

 

For example, the student management information system uses the student's student ID as the primary key.

 

When uniqueidentifier is used as the primary key

When database replication is required between multiple databases and between multiple network segments, we need to identify each single record in each unique identifier, you can use the uniqueidentifier column to generate a primary key if no proper attribute is used as the primary key.

 

Use identity as the primary key

Database Replication is not required. If the system is relatively small (within 50 tables), you can use the identity column to generate a primary key, which is suitable for rapid development.

 

Address: http://www.cnblogs.com/macroxu-1982/archive/2007/03/22/683852.html