0

If one column in a table has both clustered and non-clustered index defined due to any reason, is there any disadvantage in that? Just curious.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Satyajit
  • 1,971
  • 5
  • 28
  • 51
  • It wasts disk space - and doesn't give you any benefit in return ... and it's an unnecessary index that needs to be maintained when rows are inserted, updated or deleted .... – marc_s Jul 27 '15 at 16:56
  • It can be useful if the column is just part of the non-clustered index because the query processor only uses one index per table – Luc Jul 27 '15 at 17:08

1 Answers1

0

If both indices are on the same identical column or columns (and in the same order) then yes, they both provide the same select query optimization for individual record selects; and although the Clustered index, in addition, provides enhanced performance for select queries that return multiple records filtered on a range of values for that column, the non-clustered on is redundant.

But by having both in place you incur an additional write (Insert/Update/Delete) performance hit for the process of having to update two indices instead of only one.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • 1
    If the rows are huge, and there is a query that only needs the non-clustered index information (say, that column plus a couple include columns), performance can be enhanced for that query. Yes, it uses disk space, and yes, it slows down inserts and deletes and updates. But there are specific reasons why such a thing might be needed. – pmbAustin Jul 27 '15 at 17:13
  • An example of such a query might be a foreign key validation. Having a separate index can help avoid deadlocks [in some circumstances too](http://stackoverflow.com/a/7285041/73226) – Martin Smith Jul 27 '15 at 19:16
  • @pmbAustin, what you describe is called a 'covering index' and this concept is applicable for both clustered and non-clustered indices, As I understand it, (although I'm only 95% on this), for a clustered index, only the *leaf* level of the index includes all the columns. The Index pages at all other levels of the index only include data for the columns specified in the index. – Charles Bretana Jul 27 '15 at 20:38