Primary Key choice on table with unique identifier

Question

I'm reconsidering my choice of Primary Key on a table, and would like some input.

Some context: We are building a REST API, where clients access "Assets" identified by a GUID. So, the table for Asset in our database resembles this:

CREATE TABLE [dbo].[Asset](
    [Id] [uniqueidentifier] NOT NULL,
    [TypeId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Title] [varchar](1000) NULL,
    [Description] [varchar](max) NULL,
    [Created] [datetime] NOT NULL,
    [Modified] [datetime] NOT NULL,
    [PublisherFields] [xml] NULL,
    [StatusId] [int] NOT NULL,
 CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

We are experiencing a few problems with this structure, notably that the clustered index on the table becomes fragmented very quickly, and we have now reached the point where we are no longer able to rebuild it (because of Azure limitations on query time limits).

Further research suggests that using a uniqueidentifier as a clustered index is not always a good choice, because of the way that newid() doesn't generate IDs in sequence (unless you use newsequentialid(), but Azure doesn't allow this either).

The simple solution seems to be to introduce a dummy column and add the clustered index to that. Design guidelines say that good clustered indexes should be sequential and non-changing, so the simplest answer to that seems to be an Identity column. This would make my table into this:

CREATE TABLE [dbo].[Asset](
        [Id] [bigint] IDENTITY(1,1), <---- NEW
        [AssetId] [uniqueidentifier] NOT NULL, <---- Renamed
        [TypeId] [int] NOT NULL,
        [Date] [datetime] NOT NULL,
        [Title] [varchar](1000) NULL,
        [Description] [varchar](max) NULL,
        [Created] [datetime] NOT NULL,
        [Modified] [datetime] NOT NULL,
        [PublisherFields] [xml] NULL,
        [StatusId] [int] NOT NULL,
     CONSTRAINT [PK_Asset] PRIMARY KEY NONCLUSTERED <--- Now NonClustered
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )

The clustered index would also be added to the ID column. Does this seems a sensibible alternative? I might question the sense in having an IDENTITY column on the table that is not a primary key, but my reasoning is that an IDENTITY column is used to obtain the auto-incrementing nature, which suits the clustered index. It might also be confusing...when someone references an "AssetID", which column are they talking about? The [AssetId] column, or the Asset table's [ID] column?

Suggestions and alternatives welcomed.

Answer 1

There are a few things to consider here:

1. How is data most often looked up in this table?
2. How is data most often sorted in this table?
3. Does this table relate to others as the parent record (i.e. will other tables FK to the PK of this table)?

Also, please keep in mind:

1. The key fields of a Clustered Index are copied into the non-clustered indexes on the same table
2. For Clustered Indexes that are not a Primary Key (implied unique) or at least declared as UNIQUE, a hidden "uniqueifier" field is added to rows that would otherwise be duplicates.
3. NEWSEQUENTIALID() is sequential per each restart of the SQL Server service. It is possible that the starting value after a restart is less than the previous lowest value.

Hence:

• If the PK field of this table shows up as a FK field in other tables, there is a definite performance implication of choosing to use a UNIQUEIDENTIFIER instead of an INT as the FKed tables would then have a larger FK field in them.
• How many rows do you really expect to have in this table? INT is 4 bytes (compared to the 8 bytes of a BIGINT) and has a max value of 2,147,483,647. If you might have slightly over 2.14 billion items, you can also start the IDENTITY range at the min value of each datatype, which for INT is -2,147,483,648. Starting at the low-end gives you the full 4.294 billion values to use. Compared to the 8 bytes of the DATETIME field if going with the Created field, plus add in the size of another field to make it unique, or the uniqueifier for any duplicate rows.
• Since the key field(s) of the Clustered Index are included in Nonclustered indexes, that increases the chances of having a covering index without needing to INCLUDE other columns. Meaning, if you have the Clustered Index on the INT PK and a Nonclustered index on the UNIQUEIDENTIFIER, then JOINing to another table on that INT PK field while specifying the GUID value in a WHERE clause (assuming no other fields from this table are in the query) won't have to go back to the table since the Nonclustered Index will have both of the requiered fields in it. Does the Created field give the same benefit? Likely not.

IF no other tables ever JOIN to this table:

• THEN it might be ok to use the Created field as the non-unique Clustered Index and the Id field as the Nonclustered PK.
• ELSE it is typically best to add an INT (unless you need more than 4.294 billion values) IDENTITY field, AssetId, as the Clustered PK, and the Id UNIQUEIDENTIFIER field as a Nonclustered Index. Since you likely already have code referencing the UNIQUEIDENTIFIER field as Id, I wouldn't change that name.

Answer 2

As your API is build around the UUID being the key for these rows you are stuck with that as your functional primary key at this point, though that in itself is not a bad thing. Clustering on a UUID column is not recommended though due to the randomness of the incoming values - this causes a lot of page splits and fragmentation on the clustered index as you have noticed.

Your suggestion of adding a new incrementing integer key as your clustered index would work fine, though I would not make it the primary key because functionally speaking the UUID is still the primary key. Keeping the data layer and application layer in sync on this will avoid potential confusion later. Keeping the UUID as the PK will also avoid you having to change all the tables that have foreign key relationships with this one (and the code that interacts with those tables) to account for the new data type in the key.

Adding the new integer values for the clustered key in this way is essentially emulating the RID of a heap based table (one without a clustered index), though it is smaller than a RID or your UUID (at 4 bytes rather than 8 for the RID or 16 for a UUID), assuming you stick with an INT not a BIGINT, so will save space both with this key and on every non-clustered index in the table (non-clusered indexes on a table with a clustered key contain the clustered key value for every row so by going from a UUID to an INT for the clustered key you save 12 bytes per row per index). This doesn't mean you should always use INTs as your clustered keys as there are a several other considerations beyond index size that affect the best choice of clustered key for any given table (for instance: if you have a candidate key in your real data that is likely to be subject to range queries than this is often, but again not always, the best choice).

Answer 3

Although I don't work in Azure I would certainly suggest, for SQL Server to use a sequentially, incrementing key like newsequentialid but since you say that Azure doesn't support it... following Kimberly Tripp's advice on http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

The identity seems to be the better choice to avoid fragmentation.

Answer 4

Consider the [Date] or [Created] column for a clustered, non-unique, index key (especially if they are increasing columns) and keep the [TypeID] column as a non-clustered, unique, key.

Clustered index keys don't have to be unique - SQL Server will add a uniquifier to non-unique values. As long as most of the values are distinct, this shouldn't noticably impact performance.