Should every User Table have a Clustered Index?

by

It’s hard to state this more succinctly than SQL Server MVP Brad McGehee:

As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. In many cases, the primary key is the ideal column for a clustered index.

BOL echoes this sentiment:

With few exceptions, every table should have a clustered index.

The reasons for doing this are many and are primarily based upon the fact that a clustered index physically orders your data in storage.

  • If your clustered index is on a single column monotonically increases, inserts occur in order on your storage device and page splits will not happen.

  • Clustered indexes are efficient for finding a specific row when the indexed value is unique, such as the common pattern of selecting a row based upon the primary key.

  • A clustered index often allows for efficient queries on columns that are often searched for ranges of values (between, >, etc.).

  • Clustering can speed up queries where data is commonly sorted by a specific column or columns.

  • A clustered index can be rebuilt or reorganized on demand to control table fragmentation.

  • These benefits can even be applied to views.

You may not want to have a clustered index on:

  • Columns that have frequent data changes, as SQL Server must then physically re-order the data in storage.

  • Columns that are already covered by other indexes.

  • Wide keys, as the clustered index is also used in non-clustered index lookups.

  • GUID columns, which are larger than identities and also effectively random values (not likely to be sorted upon), though newsequentialid() could be used to help mitigate physical reordering during inserts.

  • A rare reason to use a heap (table without a clustered index) is if the data is always accessed through nonclustered indexes and the RID (SQL Server internal row identifier) is known to be smaller than a clustered index key.

Because of these and other considerations, such as your particular application workloads, you should carefully select your clustered indexes to get maximum benefit for your queries.

Also note that when you create a primary key on a table in SQL Server, it will by default create a unique clustered index (if it doesn’t already have one). This means that if you find a table that doesn’t have a clustered index, but does have a primary key (as all tables should), a developer had previously made the decision to create it that way. You may want to have a compelling reason to change that (of which there are many, as we’ve seen). Adding, changing or dropping the clustered index requires rewriting the entire table and any non-clustered indexes, so this can take some time on a large table.

More Related Contents:

  1. Why use the INCLUDE clause when creating an index?
  2. SQL Server Configuration Manager cannot be found
  3. SQL Server 2008 Spatial: find a point in polygon
  4. Using merge..output to get mapping between source.id and target.id
  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. What columns generally make good indexes?
  11. How to set a default value for an existing column
  12. An attempt was made to access a socket in a way forbidden by its access permissions
  13. How do I find a stored procedure containing ?
  14. An attempt to login using SQL authentication failed
  15. SQL Server, the misleading XLOCK & optimizations
  16. How to find SQL Server running port?
  17. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  18. Changing SQL Server collation to case insensitive from case sensitive?
  19. Unable to login to SQL Server + SQL Server Authentication + Error: 18456
  20. What’s the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?
  21. Database design: Calculating the Account Balance
  22. Sql Server replication requires the actual server name to make a connection to the server
  23. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  24. INFORMATION_SCHEMA vs sysobjects
  25. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  26. Merge multiple rows into a single row
  27. How to wait for 2 seconds?
  28. Select SQL Server database size
  29. T-SQL for finding Redundant Indexes
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment