0

Just wondering about indexes. It's a quite new concept for me and I can't see the difference between declaring columns in ON clause and in INCLUDE clause. Examples below should explain the matter:

CREATE INDEX ix_client_info1
ON dbo.client(name, age, city)
WHERE name = 'A%';

CREATE INDEX ix_client_info2
ON dbo.client(name)
INCLUDE(age, city)
WHERE name = 'A%';

How these two indexes differ in behaviour? Which to use in which situations?

GMB
  • 216,147
  • 25
  • 84
  • 135
Limak
  • 47
  • 1
  • 8
  • For some additional reading see [covering index](https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/). – HABO Feb 19 '20 at 20:05

1 Answers1

1

The difference is that included columns are not ordered in the index; they don’t belong to the index tree, they are just added to the leafs.

So this basically means two things:

  • the index is a bit smaller, and there is less overhead to maintain it

  • the index will not help if included columns are used for filtering (like in the where clause, on clause of a join, or in the group by clause) or for sorting (like in the order by clause); it is only useful when the columns are used solely in the select clause so this comes handy when you want a covering index)

There are not many cases when included columns are useful, unless you are doing advanced optimization. In general, you want regular columns keys.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thanks. For future seekers, here more about the topic: https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index – Limak Feb 19 '20 at 19:49