GUID nonclustered index

Posted by Joe Enos on August 04, 2021 · 2 mins read

Need a globally unique key for a table, but don’t want to risk performance issues arising from a random clustered index? And you don’t want to deal with similar keys by using newsequentialid?

The solution is to make a clustered index out of a regular identity integer, and use a GUID as a nonclustered primary key:

Identity is the clustered index, but not the primary key.
Primary key has a non-clustered index.
The records are physically inserted in the database in order by the clustered key, but the primary key as a random GUID is still indexed.

create table dbo.SomeTable
CX int not null identity
,SomeTableKey uniqueidentifier not null
alter table dbo.SomeTable
add constraint PK_SomeTable primary key nonclustered (SomeTableKey);
create unique clustered index CIX_SomeTable on dbo.SomeTable (CX);
alter table dbo.SomeTable
add constraint DF_SomeTable_Key default (newid()) for SomeTableKey;