0

I have an index on column A of my table. I have an index on column B of same table.

Is there any real benefit in creating an index on A and B (A, B)?

In other words, when I run a SQL query to get something based on both columns, SQL Server will automatically use the existing indexes, right? So I don't need to create another index on A & B.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kam Mistry
  • 105
  • 8
  • In many cases, a query involving `a` and `b` can benefit more from a compound index compared to separate indexes. I am not even sure MSSQL supports it, but assuming it does (MySQL does at least), the use of multiple indexes at very least involves the merging of the sections of each index. Whereas the use of a compound index is more direct. Even when the `b` in a compound index on `(a, b)` is not used for filtering or sorting, if the only fields included from that table are `a, b` then direct access to the table can be skipped entirely. – Uueerdo Jan 24 '20 at 21:45
  • Does this answer your question? [Multiple Indexes vs Multi-Column Indexes](https://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes) – Max Zolotenko Jan 24 '20 at 22:16
  • In my experience it really depends on your query (or queries) and your table's structure and workload. On occasion the query optimizer does not always uses the index you would want it to use, especially when a table is over-indexed, so it is worth investigating what kind of query plans you are dealing with. – Thailo Jan 24 '20 at 22:49
  • The compound index can be used for queries with A or with A and B, but not with b alone. – Dan Guzman Jan 24 '20 at 22:55

0 Answers0