SQL SERVER – Hint on Using uniqueidentifier or GUID Data Type

Question: What is GUID?

Question: How to create Uniqueidentifier with Example?

Question: Drawback of Uniqueidentifier Data type?

Question: What is Size of UniqueidentifierData type?

 

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). Where as int datatype contain the 4- bytes.

The uniqueidentifier data type does not automatically generate new IDs for inserted rows in the way the IDENTITY property does. For example, to obtain new uniqueidentifier values, a table must have a DEFAULT clause specifying the NEWID or NEWSEQUENTIALID function, or INSERT statements must use the NEWID function.

Uniqueidentifier can only 36 character.

Example:

Use AdventureWorks

Go

-- creating table with UniqueIdentifier

CREATE TABLE TestTab

   (MyUniqueColumn   UNIQUEIDENTIFIER  DEFAULT NEWID(),

    Name      VARCHAR(30) )

GO

-- inserting Some records in created TestTab table

INSERT INTO TestTab(Name) VALUES ('Jainendra')

INSERT INTO TestTab VALUES (NEWID(), 'Jai Verma')

GO

-- Selecting row from table

select * from TestTab

Result:

MyUniqueColumn                           Name

------------------------------------     -----------

C0E91EDB-D8F9-4B85-8ED5-E0494BD3AC49     Jainendra

A8C9D113-A1FC-421E-B8F9-FE91B0DA9604     Jai Verma

(2 row(s) affected)

  

--Clear data

Drop table TestTab

 Limitation and Drawback:

1.        The values are long.

2.        It is not good for query optimization

3.        it’s slow the performance.

4.        This makes them difficult for users to type correctly.

5.        More difficult for users to remember.

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

7.        There is no way to determine the sequence in which uniqueidentifier values were generated.

8.        They are not suited for existing applications that depend on incrementing key values serially.

9.        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.

·        Uniqueidentifier can only 36 characters  

See the Example and how to work it:

Step 1

   DECLARE @myGUIDid uniqueidentifier = NEWID();

SELECT CONVERT(char(255), @myGUIDid) AS 'MyGUIDValue';

Result:

MyGUIDValue

----------------------------------------------------

436F7702-0B72-490F-B36F-02819F2A361E

(1 row(s) affected)

Step 2

DECLARE @ID nvarchar(max) = N'436F7702-0B72-490F-B36F-02819F2A361EExtraString';

SELECT @ID, CONVERT(uniqueidentifier, @ID) AS MyTruncatedValue;

Wrong value                                                                                                                            

-------------------------------------------------- --------------------

436F7702-0B72-490F-B36F-02819F2A361EExtraString  

MyTruncatedValue

-------------------------------------------------- --------------------

436F7702-0B72-490F-B36F-02819F2A361E

(1 row(s) affected)