Unique identifier in SQL Server


How to use it ?

CREATE TABLE TEST_TABLE
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,COL2 VARCHAR(100)

)
Instead of NewID(), we can also use NewSequentuialID(). This will give a sequential value for the GUID

What is it?

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites

A uniqueidentifier value is not typically defined as a constant. You can specify a uniqueidentifier constant in the following ways:

  • Character string format: ‘6F9619FF-8B86-D011-B42D-00C04FC964FF’
  • Binary format: 0xff19966f868b11d0b42d00c04fc964ff

What are the disadvantages ?

  • The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
  • The values are random and cannot accept any patterns that may make them more meaningful to users.
  • There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
  • At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key.

How to get the last generated value?

If we are using an auto increment identity column, then we can have the last generated value in @@Identity. But we will not be able to get this if we use GUID. We may need to have it stored in a variable and insert as a workaround.

TSQL Details..

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 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, in which each 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 be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

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

Advertisements

2 thoughts on “Unique identifier in SQL Server

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s