MS SQL GUID

(Transferred from: http://blog.csdn.net/maonongwu/article/details/6327093)

GUID Introduction

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

The format of the GUID is "xxxxxxxx-xxxx-xxxx-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. GUIDs are primarily used to assign identifiers that must be unique in a network or system that has multiple nodes, multiple computers, and more than one computer. On the Windows platform, GUIDs are widely used: Registry, class and interface identifiers, databases, even automatically generated machine names, directory names, and so on.

When I developed the ASP, I used a large number of ID columns of type GUID as keywords (keys) for each entity table. Because of its unique, easy-to-produce features, it brings many benefits to application processing.

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, and several ways to use this function are as follows:

1) as the default value for the column

Set 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) Get the GUID value in advance

For special functions, you need to know the ID value of the new row beforehand, or you can use the following C # code to get the value of the GUID 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 perform arithmetic operations, but it can carry out (insignificant) comparison operations and NULL checks; it cannot, like an IDENTITY column, be aware of the order in which each row is incremented, and can only be done by adding other 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 for GUID structure include:

1) Guid.NewGuid ()

To generate a new GUID unique value

2) guid.tostring ()

Converts a GUID value into a string for easy handling

3) constructor Guid (string)

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

At the same time, the. NET Framework provides a SQLGUID structure to suit the needs of using GUIDs in the database, which is similar to the GUID structure, except that the two methods of sorting (COMPARETO) are handled differently, SqlGuid computes the last 6 bytes of a value. While the Guid calculates all 16 bytes, this difference may have some effect on the ordering of uniqueidentifier columns in SQL Server, which is of course not very meaningful.

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

3. The advantages and disadvantages of GUIDs

1) Advantages

Compared to the IDENTITY column, the uniqueidentifier column can know the new row ID in advance with the NewID () function, which provides great convenience for subsequent processing of the application.

Easy to migrate databases, other databases do not necessarily have an IDENTITY column, and GUID columns can be converted to other databases as character columns, while storing the GUID values generated in the application into the database, it 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 loaded with T-SQL.

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

2) Disadvantages

The GUID value is longer, not easy to remember 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 GUID has a value of 16 bytes, which is relatively large compared to other integers such as 4 bytes. This means that if you use the uniqueidentifier key in your database, you may have two negative effects: increased storage space and slower indexing time.

Here's a function to generate a GUID:

private String GetGuid ()

{

System.Guid GUID = new GUID ();

GUID = Guid.NewGuid ();

String str = GUID. ToString ();

return str;

}

Randomly generate the following string:

E92b8e30-a6e5-41f6-a6b9-188230a23dd2

Format Description:

System.Guid.NewGuid (). ToString (format)

Format specifier

Format of the return value

N 32 bits:

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

such as: E92B8E30A6E541F6A6B9188230A23DD2

D 32 digits separated by hyphens:

Xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

such as: E92B8E30-A6E5-41F6-A6B9-188230A23DD2

A 32-digit number enclosed in curly braces, separated by hyphens:

{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}

such as: {E92B8E30-A6E5-41F6-A6B9-188230A23DD2}

P enclosed in parentheses, 32 digits separated by hyphens:

(XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX)

such as: (E92B8E30-A6E5-41F6-A6B9-188230A23DD2)

MySQL can use the UUID () statement to generate a UUID:

Select UUID ();
or select replace (UUID (), '-', ');

Insert the UUID directly into the INSERT statement as a primary key usage (simple):

INSERT INTO price (Name, uuid, price, BID) VALUES (' Feifei_test ', uuid (), 32, 3);

Uuid
The UUID meaning is a universal unique identifier (universally unique Identifier), which is a software construction standard and is also organized by the Open Software Foundation, OSF, in a distributed computing environment ( Distributed Computing Environment, DCE) part of the field.
The purpose of the UUID is to allow all elements in a distributed system to have unique identification information without the need to specify the information through the central control terminal. In this way, everyone can create a UUID that does not clash with others. In such a case, there is no need to consider the name duplication problem when the database is established. Currently the most widely used UUID is Microsoft's globally Unique Identifiers (GUIDs), while other important applications are the Linux ext2/ext3 file system, LUKS encryption partition, GNOME, KDE, Mac OS X, and so on.
is a number generated on a machine that guarantees that all machines in the same time and space are unique. Typically, the platform provides the generated APIs. Based on standard calculations developed by the Open Software Foundation (OSF), Ethernet card addresses, nanosecond-seconds, chip ID codes, and many possible numbers are used
The UUID is composed of the following parts:
(1) The current date and time, the first part of the UUID is related to the time, if you generate a UUID after a few seconds to generate a UUID, then the first part is different, the rest is the same.
(2) Clock sequence
(3) Globally unique IEEE machine identification number, if there is a network card, from the network card MAC address obtained, no network card is obtained in other ways.
The only drawback to the UUID is that the resulting string will be longer. The most common use of UUID for this standard is the GUID of Microsoft (Globals Unique Identifiers). In ColdFusion, the UUID can be easily generated using the Createuuid () function in the form of: Xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx (8-4-4-16), where each x is 0-9 or a-f A hexadecimal number within the range. The standard UUID format is: xxxxxxxx-xxxx-xxxx-xxxxxx-xxxxxxxxxx (8-4-4-4-12), which can be converted from cflib download CreateGUID () UDF.
The benefits of using UUID are reflected in distributed software systems (e.g. DCE/RPC, Com+,corba), which ensures that the identities generated by each node are not duplicated, and that the benefits of the UUID will be more pronounced with the development of integrated technologies such as Web services. Depending on the specific mechanism used, the UUID must not only be guaranteed to be different from each other, or at least be very different from any other generic unique identifiers that were generated before the 3400 AD.
The universal unique identifier can also be used to point to most possible objects. Microsoft and some other software companies tend to use the globally unique identifier (GUID), which is a type of universal unique identifier that can be used to point to the Building object module object and other software components. The first universal unique identifier is a component created in the Snare computer system (NCS) and subsequently become a distributed computing environment (DCE) of the Open Software Foundation (OSF).
The above excerpt fromhttp://baike.baidu.com/view/1052579.htm