UniqueIdentifier

Uniqueidentifier is a data type in SQL Server, is stored natively as a 16-byte binary globally unique value. If we are saying, Uniqueidentifier is globally unique then it means Uniqueidentifier doesn’t have its duplicate value in any system of the world (but in very very less chances, exception can be there because of the procedure to generate the global unique value). The main purpose of Uniqueidentifier, is assign a value to a record to identify uniquely across the network.

A GUID value for a uniqueidentifier column is usually obtained by one of the following ways:

1)      In a Transact-SQL statement, batch, or script by calling the NEWID function.

2)      In application code by calling an application API function or method that returns a GUID.

The above mentioned method, generate global unique value based on the identification number of network card plus a unique number from the CPU clock.

The uniqueidentifier data type does not automatically generate new globally unique value for inserted records. To get the new value, we have to use newid function. The values, returned by newid function have the below format:-

SELECT NEWID()

-- RESULT
DD8567C5-E0A0-4F65-B03A-7ED50E98CB64

A table can have multiple uniqueidentifier columns.

Advantage of UniqueIndentifier : –

1)      Global Unique ID will be generated (very less chances of for duplication).

2)      Can be used when assigning an identifier that must be unique in a network that has many computers at many sites.

The uniqueidentifier data type has the following disadvantages:

1)      The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.

2)      The values are random and cannot accept any patterns that may make them more meaningful to users.

3)      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.

4)      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.

Example of Uniqueidentifier :-

-- CREATION OF TABLE HAVING COLUMN OF UNIQUEIDENTIFIER DATA TYPE

CREATE TABLE tbl (col uniqueidentifier)

-- RECORD/VALUE INSERTION

INSERT INTO tbl VALUES (NEWID())

-- GET THE RECORDS

SELECT *

FROM tbl

-- RESULT

 DAC60E7B-9457-41AD-982E-DA3ADC04C695 

reachable: agrawals1307@gmail.com