uniqueidentifier and GUID generation functions

One, uniqueidentifier is a data type that stores the value of the GUID.

uniqueidentifier data type is a 16-byte GUID.

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

• By using the NEWID function.

• By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each C10>x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Comparison operators can is used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the values. The only operations so can be performed against a uniqueidentifier value is comparisons (=, <>, <,;, & lt;=, >=) and checking for null (are null and is not NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can is used on the uniqueidentifier data type.

Merge replication and transactional replication with updating subscriptions use uniqueidentifier columns to guarantee That rows is uniquely identified across multiple copies of the table.

converting uniqueidentifier Data the uniqueidentifier type is considered a character type for the purposes of conversion from a character Expressio N, and therefore is subject to the truncation rules for converting to a character type. That was, when character expressions was converted to a character data type of a different size, values that was too long f Or the new data type is truncated.

Second, there are two ways to assign a value to a uniqueidentifier variable, which is to use a GUID to produce a function and a string assignment.

1, using the GUID to generate the function assignment

Declare @ui uniqueidentifier Set @ui = newid ()select@ui

2, using string assignment, the format of the string is 2-1-1-1-3, that is, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx '

Declare @ui uniqueidentifier Set @ui = ' aa786048-44bb-e511-80e3-f8b156cf6e62 ' Select @ui

Third, uniqueidentifier data type is used to create the table column

1,newid () for default constraint, generates a unique value for each data row

CREATE TABLE Dbo.mytable_rand (    uniqueidentifierDEFAULTNewID(),    int  ,    varchar(tengo

The NewID () function produces a GUID that is unique, and the disadvantage is that the size of the value sequence is random, not an always incremented value (ever-increasing value), and SQL Sever does not guarantee that the resulting GUID is larger or smaller than the previous GUID. If the NEWID () function is produced with a GUID as clustered index key, then the new data row is inserted in the random position, causing page split to degrade IO performance.

The best clustered index key should be incremented (increase), data type is narrow (narrow), the value is unique (unique), does not update frequently (static), NewID () The resulting GUID satisfies narrow,unique, static, but does not satisfy increase and is therefore not ideal for the clustered index key.

2, ordered GUID

The GUID value generated by the Newsequentialid () function is always incremented (ever-increasing value), and the SQL Sever guarantees that the resulting GUID is larger than the previous GUID. the Newsequentialid () function can only be used in the default constraint of the table.

If the GUID produced by the Newsequentialid () is the clustered index key, insert will insert the new row at the end of the table, and reduce page split, it is recommended to use the Newsequentialid () as clustered index key.

Creates a GUID that's greater than any GUID previously generated by this function on a specified computer since Windows W As started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using Newsequentialid can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using Newsequentialid also helps to completely fill the data and index pages.

Newsequentialid () can only is used with the DEFAULT constraints on table columns of type uniqueidentifier.

You can use Newsequentialid to generate GUIDs to reduce page splits and random IO on the leaf level of indexes.

Each GUID of generated by using the Newsequentialid is a unique on that computer.

CREATETABLEDbo.mytable (ColumnAuniqueidentifierDEFAULTNewsequentialid (), COLUMNBInt, COLUMNCvarchar( ten)) goinsert into dbo.mytable (COLUMNB,COLUMNC)values (1,'a'), (2,'  C')goSelect *from dbo.mytablego      

Four, uniqueidentifier's ROWGUIDCOL property

The ROWGUIDCOL property marks the column of a uniqueidentifier data type, which can be referenced using the $ROWGUID , and a table can have only one uniqueidentifier Column has the ROWGUIDCOL property.

CREATE TABLE Dbo.mytable_rowguidcol (    uniqueidentifierRowGUIDColnotnull            constraintDEFAULTNewID(),    int,     varchar (tenGo

Reference $ROWGUID View the column labeled ROWGUIDCOL

Select $ROWGUID  from Dbo.mytable_rowguidcol

Indicates the new column is a row GUID column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. Applying the ROWGUIDCOL property enables the column to be referenced using $ROWGUID. The ROWGUIDCOL property can is assigned only to a uniqueidentifier column. user-defined data type columns cannot is designated with ROWGUIDCOL.

The ROWGUIDCOL property does isn't enforce uniqueness of the values stored in the column. ROWGUIDCOL also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either with the NEWID or Newsequentialid function on INSERT statements or use th ESE functions as the default for the column.

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ms187942 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/ms190348 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/ms189786 (v=sql.110). aspx

uniqueidentifier and GUID generation functions