CODEWITHSUNDEEP
sqlsql-serverdatabase-administrationclustered-indexnon-clustered-index
Diego Martins
Diego Martins

Reputation: 109

Clustered index vs. Non-clustered index including ALL columns (SQL Server)

A brief overview of the scenario:

My database uses GUID's as primary keys, and, for what I've been reading, it seems like it's somewhat bad to have clustered indexes on GUID's (increases fragmentation, slows down inserts etc.). My project uses hibernate so we usually deal with jpql and fetching of full entities (a lot of queries end up turning into select p.* from person p [...])

I would like to know if it would be a good approach to create non-clustered indexes covering all columns of a table (in order to avoid RID lookups, etc.).

Thanks for the help, already!

Upvotes: 1

Views: 1237

Answers (2)

Vulcronos
Vulcronos

Reputation: 3456

Premature optimization is a bad idea. Is the data size cost and effort added to inserts, updates, and deletes worth adding the index? Unless you measure and test performance and the impact of your index, you won't know. Look at the queries that read the table and see which, if any, are unacceptably long. Then tune that specific query.

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

No, it is not a good approach. It sounds like you've already read that having the clustered index on a GUID is a bad idea. Instead, create an int (or bigint, if necessary) identity field and make that the clustered index, unless another field makes more sense. Then just create a nonclustered index on the GUID field, and let SQL do an RID lookup for each query that uses it. This way you can avoid fragmentation and slow inserts/updates/deletes.

Upvotes: 2

Related Questions