Clustered vs Non Clustered Indexes for a Primary Key

Question

I have a large table with around 19 million records in it.

The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these GUID values.

The table currently has no clustered index on it.

I am considering changing from a nonclustered index to a clustered index, since the table currently has no clustered index. I am wondering what are the pros and cons of doing this or if I should?

1. Leave things as is
2. Find another column to create the clustered index on
3. Change this ID column over from a non clustered index to a clustered index.

We are getting about 50K inserts daily, with no deletes and no updates. Let me know if you need any additional details.

The table has 24 columns total and 5 nonclustered indexes without a clustered index:

• Index 1- PK on ID (uniqueidentifier)
• Index 2- TimeStamp (datetime)
• Index 3- Hash Column (nvarchar(64))
• Index 4- Covering index 4 columns
• Index 5- Covering index 6 columns

I inherited the database from someone else. The application uses this GUID as a primary key with relationships to matching foreign keys in respective child tables so the queries ​use the uniqueidentifier id's in joins.

How do I determine if my GUID is generated sequentially? They don't appear similar. Some examples are as follows ordered by createddate:

24A689A0-831E-4670-9204-766256C58E43
ED001C69-11BA-430D-ACF9-3C3CADF3B400
768E5497-4F48-4C65-87D9-E9C27066708E

---

Doing a little research, it seems like a uniqueidentifier column is not a good candidate for a clustered index, so I'm wondering if I need to:

1. Look to a few columns to create a composite clustered index on these columns or
2. Live without a clustered index
3. Something else?

Let me know if I can provide any additional details. If anyone can give me some guidance as to what are some considerations for making this decision, I would appreciate that greatly.

Answer 1

Unless you switch to sequential GUID generation, you don't want the GUID as a clustered index key. So your choices are

1) Make the ID column the clustered index key. This will optimize joins and lookups on ID, but it sounds like those are rare.

2) For SQL 2016+ replace the Heap with a Clustered Columnstore index. This compresses the data and optimizes table scans, perhaps to the extent that you can drop some of your other indexes.

3) Pick your most used non-clustered index and make that the Clustered index.

4) Leave it as a Heap.

In any case, the "child tables" that use the GUID as a foreign key might benefit from having that foreign key column as the leading column in their clustered primary key.

Answer 2

I would not recommend using this uniqueidentifier field as the clustering key for this table. You mentioned:

we are getting about 50K inserts daily with no deletes and no updates

Making this the clustered index for your table means that the entire table will be ordered by that field. So each time an insert is done, SQL Server will need to seek to that place in the index, and then insert the new row into the table at that position.

And if there's no room on the data page where the new row needs to be inserted, then data will have to be shifted around to make room.

Ideally your clustering key would be small, unique, and ever-increasing. The uniqueidentifier definitely fails the "ever-increasing" criteria.

---

Since it sounds like the "write" portion of your workload is insert-only (no deletes or updates), a heap (no clustered index at all - the current state of things) may be a good choice for this table! As long as any SELECT queries are performing acceptably.

If you are having issues with slow select queries, you may need to add additional nonclustered indexes to support them - or select a clustered index that meets the requirements I mentioned above.