CODEWITHSUNDEEP
sqlsql-server
NorthFork
NorthFork

Reputation: 665

I don't plan to use a clustered index on my table, will I regret it?

For simplicity, let's say I have a table 'Car' in Sql Server. It has 2 columns. 'Id' is a uniqueidentifier/Guid and is the primary key. 'Name' is a nvarchar/string. The database will be for a lightly used app that peaks at maybe 10 concurrent users. 'Car' could have thousands of rows. It will be queried, inserted, and updated regularly.

I know it's bad in general to have a clustered index on a Guid column, so my plan is to leave the table as a heap and have no clustered indexes. I'd have a non-clustered index on Id.

In this very simple scenario, is there any reason I would regret not having a clustered index? If you say yes, please explain the reasoning behind your answer. I've seen posts where people say things like "I'd add an int column just to add a clustered index". I can't figure out why anyone would do that if you don't plan on querying against the int column anyways, what value does it add?

Also for this example, please assume newsequentialid() isn't an option. I'm using Entity Framework model first and it's a pain to use (unless someone can point to an easy way to do this I missed). Also assume a Guid PK is a requirement (it's an existing system).

Upvotes: 1

Views: 141

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

With a GUID as the primary key, a high percentage of inserts will cause a page split in the index. The bad kind of page split, which also leads to fragmentation. You can defer some of that with a looser fill factor, but that is still just a deferral, and forces you to use more space in the meantime.

When an IDENTITY column is clustered, and assuming you don't reseed or use SET IDENTITY_INSERT ON, it forces new inserts to the end of the table, eliminating these page splits. (Arguably this causes a different problem - "insert hot-spots" - but with 1000s of rows I don't think this will be a major concern.)

Without a clustered index on the table, you are likely going to open yourself up to performance issues involving forwarded rows.

If you are tied to GUIDs, you should consider NEWSEQUENTIALID() to avoid these issues, and cluster on that. If you are not tied to GUIDs, you should consider an IDENTITY column instead of a GUID, and cluster on that. I see no gain in not having a clustered index, except that you avoid the page splits on the base table.

Upvotes: 8

Related Questions