I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this, but let's approach this theoretically.

Theoretically speaking:

  1. If we have a very large database (150M+ rows on several tables)
  2. And we can assume the database will only be populated once.

Could indexing every possible column combination have a negative performance impact on a select query?

  • 4
    Every possible combination is impractical most oft the time. A more sensible approach is to index manually but very generously. That definitely can make sense.
    – usr
    Commented Jun 13, 2018 at 18:49
  • 12
    I suggest rewording either your title or your bolded text so they are consistent. At a glance I was confused by the highest voted answer "Yes"
    – aaaaaa
    Commented Jun 13, 2018 at 20:01
  • 1
    150M rows is large for a single table, but is not large for a database. Practically speaking, reporting systems only use a small subset of possible column combinations, it is best to focus on the key combinations at least initially, and then get more complex only as needed.
    – pojo-guy
    Commented Jun 14, 2018 at 15:27

4 Answers 4


Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider.

Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead?

That seems to be the ideal solution to your need to index every possible column combination.

Columnstore indexes - Overview

  • Columnstore is where I would go too, but I am just wondering... isn't the optimizer working just the opposite to what you described? I mean instead of scanning available indexes and "wondering" which of them could be useful doesn't it egzamin the query and "think of" a perfect index for that query, then it checks if it exists? (If it doesn't then a missing index message is generated.) If I am right (I don't know, just guessing), then even if there are thousends of indexes it should't be noticeably longer time than having just several of them.
    – Limonka
    Commented Aug 20, 2018 at 7:07

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 indexes. Most of the indexes will never be used but will have to be taken into consideration by the optimizer. It is possible that the optimizer will choose a sub-optimal index instead of a better one. I would not take the path of generating all sorts of indexes, instead of trying to figure out what indexes that would actually be beneficial.

EDIT corrected number of possible indexes

As Jeff points out it's even worse than 2^N (power-set) since (3,2,1) is clearly different than (1,2,3). For N columns we can choose the first position in an index that contains all columns in N ways. For the second position in N-1 ways, etc. We, therefore, end up with N! different indexes of full size. None of these indexes is subsumed by another index in this set. In addition, we can't add another shorter index so that it is not covered by any full index. The number of indexes is therefore N!. The example for 10 columns, therefore, becomes 10! = 3628800 indexes and for 20 (drumroll) 2432902008176640000 indexes. This is a ridicously large number, if we put a dot for each index one mm a part, it will take a lightbeam 94 days to pass all dots. All and all, dont;-)

  • 6
    Even worse: the order of columns in the index can be important. Therefore you get a maximum of N! indexes.
    – Jeff
    Commented Jun 13, 2018 at 18:33
  • 2
    But you don't need indexes that are prefixes of other indexes.
    – Barmar
    Commented Jun 13, 2018 at 18:48
  • 3
    It's even worse. There are ASC and DESC combinations for every index. Commented Jun 13, 2018 at 20:38
  • 2
    And far worse, there are INCLUDE indexes. Commented Jun 13, 2018 at 20:39
  • 2
    And a huge number of partial indexes. Commented Jun 13, 2018 at 20:39


It's not practical to index "everything", but you can index "most" of it.

Here's the thing. If a table has N columns, then the number of possible indexes is N!. Let's say a table has 10 columns, then you don't only have 10 possible indexes, but 10!. That is... 3,628,800... on a single table. That's a lot of disk space, disk I/O, cache, and seek times.

Why? A few reasons:

  • Lightwwight indexes are usually cached, something that makes them lightinng fast. If you have 3 million of them, they are NOT going to be cached.

  • The SQL optimizer may take a lot of time deciding which one is better to use, specially when using joins.

  • The SQL optimizer may give up on using the comprehensive algorithm, and try a heuristic algorithm instead. This may be "less than optimal". PostgreSQL, for example, has different options for "less-than-8 table queries", and "more-than-8 table queries".

  • Indexes are supposed to be lighter than the heap. If you are indexing everything, then the index becomes as heavy as the heap... something that defeats the purpose of the index.

  • Isn't the number 2^10? Each column is either included or excluded from a given index. Does the order matter? Commented Jun 14, 2018 at 8:52
  • 2
    @RemcoGerlich yes, the order matters. Commented Jun 14, 2018 at 9:30

No, it probably won't have negative impact to the SELECT queries, but

  • It will cause a high disk usage.
  • It will hugely increase the INSERT costs.
  • Most of your indices won't be ever used.
  • Many WHERE condition expressions still won't use indices, mainly the more complex ones.
  • The count of the required indices will increase exponentially with the count of the columns. I.e. if you have, for example, 8 columns, you need 256 indices for all the possible combinations.
  • It can totally cause an issue for compile time. Commented Jun 13, 2018 at 17:48
  • @sp_BlitzErik Do you think to the ORM in the app?
    – peterh
    Commented Jun 13, 2018 at 17:50
  • No, see my answer. Commented Jun 13, 2018 at 17:51
  • @sp_BlitzErik Wow, nice to see!
    – peterh
    Commented Jun 13, 2018 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.