SQL Server nonclustered indexes
indexing, sql-server
Solution
The fewer indexes you have - the better. Each index might speed up some queries - but it also incurs overhead and needs to be maintained. Not so bad if you don't write much to the table.
If you can combine multiple columns into a single index - perfect! But if you have a compound index on multiple columns, that index can only be used if you use/need the n left-most columns.
So if you have an index on `(City, LastName, FirstName)` like in a phone book - this works if you're looking for:
- everyone in a given city
- every "Smith" in "Boston"
- every "Paul Smith" in "New York"
but it cannot be used to find all entries with first name "Paul" or all people with lastname of "Brown" in your entire table; the index can only be used if you also specify the `City` column
So therefore - compound indexes are beneficial and desirable - but only if you can really use them! Having just one index with your 6 columns does not help you at all, if you need to select the columns individually
Update: with your concrete queries, you can now start to design what indexes would help:
SELECT COUNT(recordID)
FROM tableName
WHERE zip IN (32801, 32802, 32803, 32809)
AND modelID = '32'
AND model ID IN (22, 332, 402, 504, 620)
Here, an index on (`zip, modelID`) would probably be a good idea - both `zip` and `modelID` are used in the where clause (together), and having the `recordID` in the index as well (as an `Include(RecordID)` clause) should help, too.
SELECT COUNT(recordID)
FROM tableName
WHERE stateID = '9'
AND classCode IN (3,5,9)
AND makeID NOT IN (55, 56, 60, 80, 99)
Again: based on the `WHERE` clause - create an index on (`stateID, classCode, makeID`) and possibly add `Include(RecordID)` so that the nonclustered index becomes covering (e.g. all the info needed for your query is in the nonclustered index itself - no need to go back to the "base" tables).
Problem
I am trying to figure out the best way to handle the indexes on a table in SQL Server. I have a table that only needs to be read from. No real writing to the table (after the initial setup). I have about 5-6 columns in the table that need to be indexed. Does it make more sense to setup one nonclustered index for the entire table and add all the columns that I need indexed to that index or should I set up multiple nonclustered indexes each with one column? I am wondering which setup would have better read performance. Any help on this would be great. UPDATE: There are some good answers already but I wanted to elaborate on my needs a little more. There is one main table with auto records. I need to be able to perform very quick counts on over 100MM records. The where statements will vary but I am trying to index all of the possible columns in the where statement. So I will have queries like: ``` SELECT COUNT(recordID) FROM tableName WHERE zip IN (32801, 32802, 32803, 32809) AND makeID = '32' AND modelID IN (22, 332, 402, 504, 620) ``` or something like this: ``` SELECT COUNT(recordID) FROM tableName WHERE stateID = '9' AND classCode IN (3,5,9) AND makeID NOT IN (55, 56, 60, 80, 99) ``` So there is about 5-6 columns that could be in the where clause but it will vary a lot on which ones.