SQL Server nonclustered indexes

Question

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.

Answer 1

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).