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.
Unique identifiers are one of the fundamental blocks of data modeling and well as data consumption.
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
According to the RFC, a GUID comprises six segments, this is shown as 5 segments separated by dashes with two of them combined into one. These segments are called time-low, time-mid, time-high-and-version, clock-seq-and-reserved, clock-seq-low, and node.
323F5CD7 <!--time-low -->
411E <!--time-mid -->
4EF5 <!--time-high-and-version -->
B1 <!--clock-seq-and-reserved -->
B8 <!--clock-seq-low -->
BDD4AADF292E <!--node -->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
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)
Buy us a coffee
Become a Patron