SQL Server uses the guid value to identify a data table row

The following articles mainly describe that the guid value is used as the row identifier in the SQL server data table. The GUID (Global unique identifier) is a globally unique identifier) it is a 16-byte binary value generated by the ID number on the NIC (each Nic has a unique ID number) and 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 SQL server data tables using the guid value. 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 features, it brings many benefits to application processing.

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 row in advance. You can also use the following C # code to obtain the guid value in the SQL server data table 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 in the SQL server data table 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. The value is random and unordered. Therefore, pay attention to the usage, 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 of using guid in SQL Server Data Tables far exceed those of their 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.