GUID(Globally Unique Identifier) is null or empty in SQL Server

A GUID in SQL Server can generate unique IDs with the least probability of the exact ID being generated and/or used by someone else coincidently.

Before we dive into understanding the way of generating GUIDs in SQL Server, it’s necessary to understand some important concepts about GUID itself. So, let’s understand GUID conceptually.

• Unique Identifiers are broadly defined by two acronyms – GUID (Globally Unique Identifier) and UUID (Universally Unique Identifier). Both represent the same thing – unique IDs that are unique across space and time.
• The specification and the methodology of generating GUIDs is defined by IETF and the specification is known as RFC4122.
• GUIDs can be generated in multiple ways. The most common ones are based on Random number generation, Time clock sequence, Hardware based using MAC addressed of network cards, and Content based by calculating hash of the data using common hashing algorithms like MD5, AES, SHA and others.
• GUIDs are typically 128-bit in size and 32 digits long in the pattern of 8 digits – 4 digits – 4 digits – 4 digits – 12 digits
• Using the above mechanism, a total of approximately 10³⁸ GUIDs can be generated, which are more than enough for everyone in the world to use GUIDs for trillions of data items per person.

Now that we have enough understanding of GUIDs, we can start focusing on the methods of creating GUIDs in SQL Server. There are two functions using which you can create GUIDs in SQL Server – NewID and NewSequentialID. And there’s a data type – “uniqueidentifier” which can be used to store GUIDs. It stores a 16-btye binary value.

SQL Server NEWID to Generate GUID

Let’s create a variable of uniqueidentifier data type.  Type the below code in SSMS and execute.

DECLARE @guid uniqueidentifier = NEWID();
SELECT @guid as 'GUID';			

Here we created a variable named guid of data type uniqueidentifier. To generate a unique identifier, we need to assign a default method of creating it, and for that we have used the NEWID function which generates and returns a RFC4122 compliant GUID. The output of the above code would be as shown below. This would be different when you execute it on your machine, as the GUIDs are supposed to be globally unique at any given point in time. In fact, I executed the same code twice and each time the ID that came out was different as expected. This is the entire purpose of generating GUIDs.

While creating a new table, you can create a field of uniqueidentifer data type as assign the NEWID as the default function as shown below.

CREATE TABLE Product_A
(
ID uniqueidentifier default newid(),
productname varchar(50)
)			

Insert some records into this table using the below code.

Insert Into Product_A(productname) values('A1')
Insert Into Product_A(productname) values('A2')
Insert Into Product_A(productname) values('A3')
Select * from Product_A			

The output would be as shown below.

One thing to note is regarding different operations that you can perform on GUIDs / uniqueidentifier data type. If you sort the ID field in the above table, the result is as shown below in my case. The IDs generated are not necessarily in increasing order, so if you sort the data by GUIDs, the data may not be in the same order in which you inserted. SQL Server sorts GUIDs typically from the 5^th part of the GUID towards the 1^st part, and the search algorithm is not well documented. The NewID function used randomness (random activity) as part of its GUID generation mechanism, so it cannot be guaranteed that every GUID that is generated would be in sequence or in order of higher values.

Also, only comparison operators are supported on GUIDs. For example, if you attempt to use an aggregation operator like sum, you would encounter an error as shown below.