Best index strategies for read-only table? I have a table in SQL Server which has following characteristics:


will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
the data will not be updated or deleted, just be queried
the number of concurrent query is high, each query return only one record
there's nothing to do with the ID (because of the records are not updated or deleted)
the key of queries is two field, PROVINCE_ID and CANDIDATE_NUMBER (unique across the table)


I'm thinking about creating clustered index on the ID column, and non-clustered index in (PROVINCE_ID,CANDIDATE_NUMBER). will it the best choice? Can you give me another suggestion? please explain why should I do that?

Thank you so much!
If the ID column is not used for data retrieval, I suggest you place the clustered index on the PROVINCE_ID, CANDIDATE_NUMBER columns instead.

If some columns are read very often compared to others, consider adding an index with PROVINCE_ID, CANDIDATE_NUMBER plus that column. That way the queries can get everything they need from the index itself and don't need to look at the table at all.