I've got a table in SQL Server that has following qualities:

  • will contain about 1.2M records.
  • first is empty, is going to be placed in batch many occasions (precisely, 64 occasions), 20-100k records/each
  • the information won't be up-to-date or erased, you need to be queried
  • the amount of concurrent totally high, each query return just one record
  • there is nothing related to the ID (due to the records aren't up-to-date or erased)
  • the important thing of queries is 2 area, PROVINCE_ID and CANDIDATE_NUMBER (unique over the table)

I am considering creating clustered index around the ID column, and non-clustered index in (PROVINCE_ID,CANDIDATE_NUMBER). does it the best option? Are you able to produce another suggestion? please explain why must I actually do that?

Many thanks!

When the ID column sits dormant for data retrieval, It is best to put the clustered index around the PROVINCE_ID, CANDIDATE_NUMBER posts rather.

If some posts are read very frequently in comparison to others, attempt to add a catalog with PROVINCE_ID, CANDIDATE_NUMBER plus that column. This way the queries could possibly get everything they require in the index itself and don't have to consider the table whatsoever.

Discover likely to have every other fields inside your WHERE clause than individuals two, go ahead and cluster on PROVINCE_ID, CANDIDATE_NUMBER. For those who have additional criteria you'll be choosing on, think about a covering index.

The above mentioned also assumes both individuals fields are INT data types. If they're string types (varchar, char, nvarchar) then create an INT IDENTITY area and cluster on that. Should you cluster on wide data types like strings, it utilizes a lot more room per row, which is increased by the amount of indexes you've (because the cluster secret is in every row of each and every non-clustered index).

Also keep in mind when you cluster on PROVINCE_ID, CANDIDATE_NUMBER and employ a WHERE clause that only filters on CANDIDATE_NUMBER, then your index will not be utilized whatsoever. Inside a data warehousing situation such as this, you generally wish to have a catalog that covers all of your queries.