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!