2 Added additional detail
source | link

A narrow, static, unique clustering key is better in situations like these:

  • The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
  • The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
  • No other field or combination of fields is guaranteed to be unique
  • Queries against the table usually retrieve one row and use a surrogate key to do so (e.g. because the query comes from an application that has learned the surrogate key, not a user who is searching by natural key)

A multi-column clustering key is better in situations like these:

  • No other table references this one (e.g. a fact table in a data warehouse)
  • Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
  • There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
  • A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations

A narrow, static, unique clustering key is better in situations like these:

  • The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
  • The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
  • No other field or combination of fields is guaranteed to be unique

A multi-column clustering key is better in situations like these:

  • No other table references this one (e.g. a fact table in a data warehouse)
  • Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
  • There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
  • A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations

A narrow, static, unique clustering key is better in situations like these:

  • The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
  • The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
  • No other field or combination of fields is guaranteed to be unique
  • Queries against the table usually retrieve one row and use a surrogate key to do so (e.g. because the query comes from an application that has learned the surrogate key, not a user who is searching by natural key)

A multi-column clustering key is better in situations like these:

  • No other table references this one (e.g. a fact table in a data warehouse)
  • Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
  • There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
  • A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations

1
source | link

A narrow, static, unique clustering key is better in situations like these:

  • The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
  • The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
  • No other field or combination of fields is guaranteed to be unique

A multi-column clustering key is better in situations like these:

  • No other table references this one (e.g. a fact table in a data warehouse)
  • Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
  • There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
  • A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations