MS SQL Server

SQL Server GUID

In this post, we will learn how to use the uniqueidentifier type in SQL Server. We will also use the NEWID() and NEWSEQUENTIALID() functions to generate GUID values.

SQL Server Uniqueidentifier Type

This is a 16-byte GUID value used in a column or a local variable. You can create a uniqueidentifier type value using the NEWID() and NEWSEQUENTIALID() functions.

You can also generate a GUID value by converting a string value in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx where x is a hexadecimal digit in the range of 0 – 9.

Due to the ‘randomness’ of a GUID value, it is guaranteed that a GUID value can be unique across a database or even servers. This makes an excellent data type for uniquely identifying a given value.

SQL Server NEWID() Function

The NEWID() function allows us to generate a new unique value of the uniqueidentifier type. The syntax is as shown:

NEWID ( )

For example:

declare @gid uniqueidentifier;
set @gid = NEWID();
select @gid as gid;

The above statements should return a GUID value as:

gid
873412E2-A926-4EAB-B99F-A1E47E727355

SQL Server NEWSEQUENTIALID() Function

This function allows you to generate unique GUID values sequentially. It works by generating a GUID value that is greater than the previously generated GUID.

This makes it useful for use as a row identifier since it generates values sequentially rather than manually determining the next GUID value using the NEWID() function.

The function syntax is as shown:

NEWSEQUENTIALID ( )

Using SQL Server GUID as Row Identifier

The following example shows how to use the newsequentialid() function as a row identifier for a given column.

create table entries(
    id uniqueidentifier not null default newsequentialid() primary key,
    server_name varchar(50),
    server_address varchar(255) not null,
    compression_method varchar(100) default 'none',
    size_on_disk float not null,
    size_compressed float,
    total_records int not null,
    init_date date
);
insert
    into
    ENTRIES(server_name,
    server_address,
    compression_method,
    size_on_disk,
    size_compressed,
    total_records,
    init_date)
values
('MySQL','localhost:3306','lz77',90.66,40.04,560000,'2022-01-02'),
('Redis','localhost:6307','Snappy',3.55,998.2,100000,'2022-03-19'),
('PostgreSQL','localhost:5432','pglz',101.2,98.01,340000 ,'2022-11-11'),
('Elasticsearch','localhost:9200','lz4',333.2,300.2,1200000,'2022-10-08'),
('MongoDB','localhost:27017','Snappy',4.55,4.10,620000,'2021-12-12'),
('Apache Cassandra','localhost:9042','zstd',300.3,200.12,10000000,'2020-03-21');

In the above example, we set the ID column as the uniqueidentifier type and the default value as the value generated by the newsequentialid() function.

The resulting table is as shown:

select * from entries;

Output:

Although using GUID values can provide strict uniqueness, it can be difficult when debugging or selecting specific values.

Conclusion

In this guide, you learned about the uniqueidentifier type in SQL Server. You also learned how to generate GUID values using the NEWID() and NEWSEQUENTIALID() functions.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list