Random GUID or Sequential GUID

Random GUID or Sequential GUID
SQL Server has two in-built functions for creating uniqueidentifier.
NEWID()
NEWSEQUENTIALID()
New NEWSEQUENTIALID would help to reduce page splits and fragmentation because of sequential nature of the values returned from this function.
NEWID() is truly (almost) random and it would result in page splits and fragmentation

Lets create the table and see the results.

IF OBJECT_ID(‘Random_Guid_Fragementation’) IS NOT NULL
DROP TABLE RandomGuid_Fragementation;

CREATE TABLE RandomGuid_Fragementation
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
Column1 VARCHAR (33) DEFAULT ‘COLUMN1’,
Column2 VARCHAR (4000) DEFAULT ‘COULUMN2’
);

IF OBJECT_ID(‘Sequential_Guid_Fragementation’) IS NOT NULL
DROP TABLE Sequential_Guid_Fragementation;

CREATE TABLE Sequential_Guid_Fragementation
(
ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
Column1 VARCHAR (33) DEFAULT ‘COLUMN1’,
Column2 VARCHAR (4000) DEFAULT ‘COULUMN2’
);

DBCC IND (Dev, Sequential_Guid_Fragementation, 1);

DBCC IND (Dev, RandomGuid_Fragementation, 1);

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id(‘RandomGuid_Fragementation’), 1, NULL, NULL)
UNION ALL
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id(‘Sequential_Guid_Fragementation’), 1, NULL, NULL);

Fragmentation from Sequential_Guid_Fragementation is about 4%.
Fragmentation from RandomGuid_Fragementation is about 96%.

For masking, NEWSEQUENTIALID() won’t be good because its easier to guess next sequence. If its needed just for row identification, then its good candidate.