In previous article, I have mentioned SQL Server Switch case and SQL Server date format, now in this article, I have going to explain what is uniqueidentifier in SQL Server and how we can use uniqueidentifier in sql server to save a unique value in column and how to create/save it. Let's understand each topic one by one.

What is Uniqueidentifier in SQL Server?

GUID (Globally Unique Identifier) or  (UUID) Universally Unique Identifier is a 16 byte binary value represented as UNIQUIEIDENTIFIER data type, these type of data is unqiue across the different database servers and networks, which then can be used to fetch a record based on unique value of UUID.

You can insert or save unique identifier value using .NET C# code, or you can do it with SQL Server query using newId() or newsequentialid() functions and it is guaranteed to be unique throughout the world.

GUID or UUID in SQL Server is called as Uniqueidentifier, so we will be using term GUID in this article to understand everything.

Example of GUID:

If you will run the below query

SELECT NEWID();

You will get output as 323F5CD7-411E-4EF5-B1B8-BDD4AADF292E

sql-server-uniqueidentifier-min.png

Using unqiueidentifier as Index

Although GUID or UUID is unique, for each table row, but we cannot use it as random I/O into your indexes, because it will negatively impact performance.

As GUID is long and obscure 16 byte binary values that are not meaningful values.

In general we should be using GUIDs only for very narrow scenarios when no other data type is suitable.

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

Inserting GUID as uniqueindetifier in SQL Server database column

Here is the sample query which you can use to insert uniqueindentifier in SQL Server database table column

INSERT INTO Names
(Name, UniqueID)
VALUES
('Vikas lalwani', NEWID())

Considering you have a table named as "Names", with column name = varchar type and UniqueID  = uniqueindentifier column type

insert-uniqueidentifier-sql-server-min.png

Storing UniqueIdentifier

Uniqueidentifier is a data type designed to store GUID values. In practice, however, developer can sometimes choose binary, unicode or non-unicode data types to store those values depending on their need.

The string data types require significantly more space. There are also concerns about sorting and casting, here is a details with type and storage space.

BINARY(16) – 16bytes
CHAR(36) – 36bytes
NCHAR(72) – 72bytes

Converting Uniqueidentifier

The following example converts a uniqueidentifier value to a char data type.

DECLARE @myid uniqueidentifier = NEWID();  
SELECT CONVERT(CHAR(255), @myid) AS 'char';  

SQL Server UNIQUEIDENTIFIER - Equivalents in Other Databases

PostgreSQL: UUID

Oracle: Save it RAW(16)

MySQL: Save it as CHAR(38)

You may also like to read:

How to check sql server version? (Various ways explained)

Cannot execute script: Insufficient memory Error in SQL server?

Creating SQL Server maintenance plan (Step by step procedure)

Download and Install SQL Server (Step by Step procedure)

Tools to Compare Two SQL Server Database