GUID nonclustered index

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?

One 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;

Leave a Reply

Your email address will not be published.