DEV Community

Joe Enos
Joe Enos

Posted on

GUID Primary Keys

In SQL Server, it's sometimes tempting to use a GUID as the primary key for a table. You can default it to NEWID(), to get a reasonably random key, which can be useful for data migration, reasonably unguessable URLs, all kinds of reasons.

The problem out of the box is that if you use a random primary key, by default it's also the clustered index, which means it determines how the table is physically sorted - this leads to fragmentation and performance issues. It also makes all of your other indexes a lot bigger.

The solution is to add an identity column, just to act as the clustered index. You can still make your GUID the primary key, but make it a nonclustered index:


CREATE TABLE dbo.Apps (
    CX INT NOT NULL IDENTITY
    ,INDEX IX_Apps_CX CLUSTERED (CX)

    ,AppID UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT DF_Apps_AppID DEFAULT (NEWID())
    ,CONSTRAINT PK_Apps PRIMARY KEY NONCLUSTERED (AppID)

    ,AppName NVARCHAR(100) NOT NULL
    ,INDEX IX_Apps_AppName (AppName)

    ,CreateDate DATETIME2 NOT NULL
        CONSTRAINT DF_Apps_AppName DEFAULT (SYSUTCDATETIME())
    ,INDEX IX_Apps_CreateDate (CreateDate)
);
GO
Enter fullscreen mode Exit fullscreen mode

Discussion (0)