Hello friends, UNIQUEIDENTIFIER is GUID(Globally Unique Identifier), which is unique all over the world. UNIQUEIDENTIFIER data type stores 16 bytes binary values. GUID is unique all over the world because it was created I don’t know the exact mechanism behind this but it is based on the combination of MAC address of system and time. So, it won’t duplicate ever.

A Globally Unique Identifier can be obtained by following:

• By calling NEWID function. It will return GUID.
• By calling an application API function which returns GUID.

UNIQUEIDENTIFIER is in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal value. For example, 0C922534-C7EE-44DB-B011-6D80DE781244 is a valid UNIQUEIDENTIFIER value.

UNIQUEIDENTIFIER will not be generated automatically, you will have to assign default NEWID function while creating table. For example,

CREATE TABLE #temp(
id UNIQUEIDENTIFIER DEFAULT NEWID(),
name VARCHAR(150)
)
INSERT INTO #temp(name)
VALUES('Bhavin'),('Pokiya')

SELECT * FROM #temp

Try above code in SQL Server.

The main advantage of UNIQEIDENTIFIER is that it is created using NEWID function and unique all over the world. So, you can use it while storing sensitive data.

As it has advantage that doesn’t mean it is good because it has some disadvantages :

• It is long(see the syntax). So, it will consume more memory to store. This means indexes built using this will be slower than the indexes that was created using int data type.
• As it is generated randomly, you can’t say what will be the next value of column. Try following code you will get it.
• It’s values is random. So, it is not in an understandable form for users.

Try following in SQL server. It will generate UNIQEIDENTIFIER on every F5.

DECLARE @var1 UNIQUEIDENTIFIER = NEWID();
SELECT CONVERT(char(255), @var1) AS 'unique';

Hope you liked it. Don’t forget to share your views or questions.

Thanks 🙂