How to choose the clustered index in SQL Server?

by

According to The Queen Of Indexing – Kimberly Tripp – what she looks for in a clustered index is primarily:

  • Unique
  • Narrow
  • Static

And if you can also guarantee:

  • Ever-increasing pattern

then you’re pretty close to having your ideal clustering key!

Check out her entire blog post here, and another really interesting one about clustering key impacts on table operations here: The Clustered Index Debate Continues.

Anything like an INT (esp. an INT IDENTITY) or possibly an INT and a DATETIME are ideal candiates. For other reasons, GUID’s aren’t good candidates at all – so you might have a GUID as your PK, but don’t cluster your table on it – it’ll be fragmented beyond recognition and performance will suffer.

More Related Contents:

  1. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  2. SQL Server add auto increment primary key to existing table
  3. Database design: Calculating the Account Balance
  4. “Prevent saving changes that require the table to be re-created” negative effects
  5. What do Clustered and Non-Clustered index actually mean?
  6. SQL Server, How to set auto increment after creating a table without data loss?
  7. How can I truncate a datetime in SQL Server?
  8. Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  9. How to add Active Directory user group as login in SQL Server
  10. How to create materialized views in SQL Server?
  11. Why use the INCLUDE clause when creating an index?
  12. What columns generally make good indexes?
  13. How to set a default value for an existing column
  14. An attempt was made to access a socket in a way forbidden by its access permissions
  15. How do I find a stored procedure containing ?
  16. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  17. Should every User Table have a Clustered Index?
  18. An attempt to login using SQL authentication failed
  19. How to set monday as first day of week in SQL Server
  20. SQL Server, the misleading XLOCK & optimizations
  21. Drop primary key using script in SQL Server database [duplicate]
  22. SQL Unique constraint across multiple tables
  23. How to find SQL Server running port?
  24. Changing SQL Server collation to case insensitive from case sensitive?
  25. How to store directory / hierarchy / tree structure in the database?
  26. The request failed or the service did not respond in a timely fashion?
  27. Merge multiple rows into a single row
  28. ‘Incorrect SET Options’ Error When Building Database Project
  29. How to convert text column to datetime in SQL
  30. Why insert-select to variable table from XML variable so slow?

Leave a Comment