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!

  • 1
    How many other fields are there in the table? Will the queries most likely only read some of them, or all of them equally?
    – Tom
    Apr 12 '11 at 10:23
  • @Tommi: it's about 10 other fields, such as first name, family name, point1,2...6, total point, all of them are read equally @Mitch Wheat: I don't think 54% is a bad rate. Whenever an answer solves my problem, I accept it right away. If it does not, why should I do? Please look at my list of questions for that
    – Vimvq1987
    Apr 12 '11 at 10:34
  • 1
    Often the quality of the questions or unrealistic expectations are factors. I consider 54% with 99 questions to be very low. For instance, you left out what should have been some obviously required info in this question initially. Apr 12 '11 at 10:41
  • 1
    @Andrew Barber is right. IF you have asked 100 questions and barely half have satisfactory answers, the problem isn't everyone else :)
    – JNK
    Apr 12 '11 at 12:11

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.

  • If he clusters on Province_ID, Candidate_Number, all the data will be in the index regardless (that's the nature of a clustered index).
    – JNK
    Apr 12 '11 at 12:03

If you aren't going to ever have any other fields in your WHERE clause than those two, by all means cluster on PROVINCE_ID, CANDIDATE_NUMBER. If you have additional criteria you will be selecting on, consider a covering index.

The above also assumes both those fields are INT data types. If they are string types (varchar, char, nvarchar) then create an INT IDENTITY field and cluster on that. If you cluster on wide data types like strings, it uses a lot more space per row, and that is multiplied by the number of indexes you have (since the cluster key is in each row of every non-clustered index).

Also bear in mind that if you cluster on PROVINCE_ID, CANDIDATE_NUMBER and use a WHERE clause that only filters on CANDIDATE_NUMBER, then the index won't be used at all. In a data warehousing situation like this, you generally want to have an index that covers all your queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.