Sql-server – Best index strategies for read-only table

database-designindexsql server

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!

Best Answer

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.

Related Question