Use the guid value as the database row ID

GUID (Global unique identifier) is a globally unique identifier, which consists of the ID numbers on the NIC (each Nic has a unique ID number) and a 16-byte binary value generated by the unique number of the CPU clock.

The GUID format is "XXXXXXXX-XXXX-xxxxxxxxxxxx", where each X is a hexadecimal number in the range of 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. GUID is used to assign a unique identifier to a network or system with multiple nodes and computers. On Windows, GUID is widely used: Registry, class and interface identifiers, databases, and even automatically generated machine names and directory names.

In this ASP. NET application development, I used a large number of ID columns with guid type as the keywords (KEYS) of each entity table ). Because of its unique and easy-to-generate featuresProgramProcessing brings many benefits.

1. Use guid in SQL Server

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

The newid () function in SQL Server can generate unique guid values. The following methods are used to use this function:

1) As the column Default Value

Set the default value of the uniqueidentifier column to newid (). In this way, the guid value of this column is automatically generated when a new row is inserted into the table.

2) use T-SQL

Use the newid () function in the T-SQL, such as insert into table (ID,...) values (newid (),...) to generate the guid value for this column.

3) Get the guid value in advance

For special functions, you need to know the id value of the new line in advance. You can also use the following C #CodeGet the guid value in advance and 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 ();

The uniqueidentifier value cannot be used for arithmetic operations, but can be used for (not significant) Comparison operations and null checks. It cannot be the same as the identity column, and can be used to know the sequence of time increase for each row, this function can only be completed by adding other time or timestamp columns.

2. Use guid in. net

GUID is widely used in. net, and. NET Framework provides a dedicated guid infrastructure.

Common guid structure methods include:

1) guid. newguid ()

Generate a new guid unique value

2) guid. tostring ()

Converts a guid value to a string for processing.

3) constructor GUID (string)

The GUID structure is generated by the string, where the string can be in upper or lower case. It can contain the delimiters "{}" or "()" at both ends. you can even omit the "-" in the middle. There are many constructors in the guid structure, and other constructors are not commonly used.

In addition, to apply the guid in the database ,. NET framework also provides the sqlguid structure, which is similar to the guid structure, but the two have different processing methods for sorting (compareto). sqlguid calculates the last 6 bytes of the value. However, if GUID is calculated for all 16 bytes, this difference may affect the sorting of uniqueidentifier columns in SQL Server. Of course, this sort is of little significance.

In. NET Framework, you can use the guidconverter class to provide a type converter that converts the guid structure with various other representations.

3. Advantages and Disadvantages of guid

1) Advantages

• Compared with the identity column, the uniqueidentifier column can use the newid () function to know the newly added row ID in advance, which provides great convenience for subsequent processing of the application.

• It is easy to port the database. Other databases do not necessarily have the identity column. The GUID column can be converted to other databases as a sort column, and the guid value generated by the application is saved to the database, it does not affect the original data.

• To facilitate database initialization, if the application needs to load some initial data, the processing of the identity column is more troublesome, And the uniqueidentifier column does not need to be processed, directly load with a T-SQL.

• It is easy to permanently identify certain objects or constants, such as class classid, instance id of objects, contacts, service interfaces, and tModel identification definitions in UDDI.

2) Disadvantages

• The GUID value is long and is not easy to remember and input, and it is random and unordered. So pay attention to the occasion when using it. It is best not to use it as your email address.J
  
  
• The GUID value has 16 bytes, which is relatively larger than other 4-byte integers. This means that if the uniqueidentifier key is used in the database, there may be two negative effects: increased storage space and slow indexing time.

In summary, the benefits brought by guid far exceed those brought about by its shortcomings. With the continuous development of system interconnection and integration technologies such as WebService, its unique identifier makes its application more and more widely. You should also consider using it in your application.