Use a GUID value to explain the database row identity _mssql

GUID (Global unique identifier) Globally unique identifier, which is a 16-byte binary value generated by an identity number on the network card (each NIC has a unique identification number) and a unique number of CPU clocks.

The GUID is formatted as "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", where each x is a hexadecimal number in the range 0-9 or a-f. For example: 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid GUID value.

No two computers in the world will generate duplicate GUID values. GUIDs are primarily used to assign identifiers that must be unique in a network or system that has more than one node or multiple computers. On Windows platforms, GUIDs are widely used: Registry, class and interface identities, databases, and even automatically generated machine names, directory names, and so on.

During this development asp.net application, I used a lot of the ID column of type GUID as the key word for the Entity table (key). Because of its unique, easy to produce characteristics, the application processing brings many benefits.

1. Using GUIDs in SQL Server

If the column type is specified as uniqueidentifier in the table definition of SQL Server, the value of the column is the GUID type.

The NewID () function in SQL Server can produce a GUID unique value, using this function in several ways as follows:

1) as the default value of the column

Sets the default value of the uniqueidentifier column to NewID () so that when new lines is inserted into the table, this column GUID value is automatically generated.

2) using T-SQL

Use the NewID () function in T-SQL, such as INSERT into Table (ID,...) VALUES (NewID (),...) " To generate the GUID value for this column.

3 Obtain the GUID value in advance

Because of the special functionality needs, you need to know the ID value of the new row in advance, or you can use the following C # code to get the value of the GUID in advance, and then store it in the database:

SqlCommand cmd = New SqlCommand ();
Cmd.commandtext = "Select NewID ()";
String RowID = (string) cmd. ExecuteScalar ();
Cmd.commandtext = "INSERT into Table (ID,...) VALUES (@ID,...)
Cmd. Parameters.Add ("@ID", Sqldbtype.uniqueidentifier). Value = new Guid (RowID);
Cmd. Executenoquery ();

uniqueidentifier values do not perform arithmetic operations, but can perform (insignificant) comparison operations and NULL checks; it cannot be known as an IDENTITY column to know the order of increment for each line, only by adding additional time or timestamp columns.

2. Using GUIDs in. NET

GUIDs are widely used in. NET, and the. NET Framework provides a dedicated GUID infrastructure.

Common methods of GUID structure include:

1) Guid.NewGuid ()

Generate a new GUID unique value

2) guid.tostring ()

Converts a GUID value to a string for easy processing

3 constructor Guid (string)

The GUID structure is generated by string, where a string can be uppercase or lowercase, can contain both ends of the delimiter "{}" or "()", or even omit the middle "-", there are many constructors for the GUID structure, and other construction usages are not commonly used.

Also, in order to apply the need for GUIDs in the database, the. NET Framework also provides a SQLGUID structure that is similar to the GUID structure, except that the CompareTo is handled differently, SqlGuid evaluates to the last 6 bytes of the value. The Guid calculates all 16 bytes, and this difference may have some effect on the ordering of the uniqueidentifier column in SQL Server, although this sort of order is of little significance.

Class Guidconverter can be used in the. NET Framework to provide type converters that convert the GUID structure to a variety of other representations.


3, the advantages and disadvantages of the GUID

1) Advantages

Compared to the IDENTITY column, the uniqueidentifier column can know the newly added row IDs in advance through the NewID () function, providing a great convenience for subsequent processing of the application.


Facilitate database porting, other databases do not necessarily have IDENTITY columns, and GUID columns can be converted to other databases as character columns, while the GUID values generated in the application are stored in the database, which does not affect the original data.


Easy to initialize the database, if the application to load some initial data, the IDENTITY column processing is more cumbersome, and the uniqueidentifier column without any processing, directly with T-SQL load.


It facilitates the permanent identification of certain objects or constants, such as the ClassID of classes, the identity of instances of objects, the contacts in UDDI, service interfaces, tmodel identity definitions, and so on.
2) Disadvantages

GUID value is longer, not easy to memory and input, and this value is random, no order, so use to pay attention to the occasion, it is best not to try to use it as your e-mail address J


The value of the GUID is 16 bytes and is relatively large compared to other integers such as 4 bytes. This means that if you use the uniqueidentifier key in your database, it can have two negative effects: increased storage space, slower indexing time.

In combination, the advantages of GUIDs far outweigh the effects of their shortcomings, and with the development of interconnected and integrated technologies such as WebService, its unique identity makes it more widely available and should be considered in your application.