Chaos in the library

The uniqueidentifier datatype is a 16-byte binary value used as a globally uniqueidentifier (GUID). Developers use it in database models for assigning identifiers that must be unique in a network of many computers at many sites. Also, developers exploit this uniqueidentifier data type inappropriately, assuming this is always the best way to enforce uniqueness in their data model.

Because GUID values are randomly generated, it is a bad idea to use them as clustered primary keys.

A non-believer ? We’ll show you how GUID keys introduce fragmentation into your nice database. If you are not familiar with the term fragmentation then think of it as the chaos in a library that results from not re-shelving returned books: long queueing durations, resource locking situations, unhappy customers.

For the demo we need two tables, both have an integer identity and a uniqueidentifier column. In the first table the primary key is clustered on the integer identity column, in the second table the primary key is on the GUID column.

CREATE TABLE tblID (
ID int identity(1, 1) NOT NULL,
GUIDValue uniqueidentifier NOT NULL,
CONSTRAINT PK_tblID PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR = 100))
CREATE TABLE tblGUID (
ID int identity(1, 1) NOT NULL,
GUIDValue uniqueidentifier NOT NULL,
CONSTRAINT PK_tblGUID PRIMARY KEY CLUSTERED(GUIDValue) WITH (FILLFACTOR = 100))

Next, we’ll insert 1 million rows in both tables. To do this very fast, we’ll use the following custom function:

/* ——
Function : fnc_NumberRange
Date Created : 19/03/2015 – 15:45
Created By : Ronald Clauw (Kohera)
Description : This function returns a ranged table.
Usage : SELECT Seq FROM dbo.fnc_NumberRange(- 11, 20)
Date Modified :
Modified By :
Description :
—- */
CREATE FUNCTION [dbo].[fnc_NumberRange]
(@Minimum bigint, @Maximum bigint)
RETURNS TABLE
AS
RETURN
WITH
CTE01 AS
(SELECT Value FROM (VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL)) AS TenNullValues(Value)), — 10
CTE02 AS
(SELECT x.Value FROM CTE01 AS x CROSS APPLY CTE01 AS y), — 100
CTE03 AS
(SELECT x.Value FROM CTE02 AS x CROSS APPLY CTE02 AS y), — 10.000
CTE04 AS
(SELECT x.Value FROM CTE03 AS x CROSS APPLY CTE03 AS y), — 100.000.000
CTE05 AS
(SELECT x.Value FROM CTE04 AS x CROSS APPLY CTE04 AS y), — 10.000.000.000.000.000
CTE06 AS
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM CTE05)
SELECT TOP(ABS(@Maximum) – @Minimum + 1) @Minimum + RowNumber – 1 AS Seq
FROM CTE06

Now, we’ll insert 1 million rows in both tables.

INSERT INTO tblID (GUIDValue)
SELECT NEWID() from dbo.fnc_NumberRange(1, 1000000)
INSERT INTO tblGUID (GUIDValue)
SELECT NEWID() from dbo.fnc_NumberRange(1, 1000000)

A lot faster than using a loop, isn’t it ?

In SQL Server Management Studio, we look at the fragmentation percentage on both the clustered indexes (right-click the index in de Object Explorer pane > Properties > Fragmentation) :

tblGUID : 0.67%

tblID : 0.42%

Our function realizes the insertion of the 1 million rows in one batch in an initially empty table: rows get neatly ordered according to the indexed column. Almost no fragmentation there.

Let’s insert a new batch of 10 000 records in each table.

declare @MaxID int
SET @MaxID = (SELECT MAX(ID) FROM tblID)
INSERT INTO tblID (GUIDValue)
SELECT NEWID() from dbo.fnc_NumberRange(@MaxID + 1, @MaxID + 10000)
INSERT INTO tblGUID (GUIDValue)
SELECT NEWID() from dbo.fnc_NumberRange(@MaxID + 1, @MaxID + 10000)

Time to diagnose the health of our tables : let’s check the level of fragmentation again. Bam !

tblGUID : 95.55%

tblID : 0.41%

Fragmentation on the GUID clustered table is skyrocketing. What does it mean : chaos at the library !

You can insert as many batches of additional rows as you want: fragmentation on the GUID clustered index will stay high. Another drawback is that your 16-byte large GUID is part of every other non- clustered index defined on that table. And last but not least, there will be a lot of page splits.

Conclusion : Consider using the IDENTITY property when global uniqueness is not required, or when having a serially incrementing key is preferred. If you need a uniqueidentifier data type for the right reasons, then you can add it to a non-clustered index, but even then heavy fragmentation will occur.