I have a table with following 3 fields

1) Id      bigint
2) Name    varchar(100)
2) Country varchar(50)
3) State   varchar(50)
4) City    varchar(50)

In a country there are no. of states and in a state there are no. of city

I have two queries

a) If I want to know name of persons who are living in a city='Nagpur'

  1. should I use index only on column city
  2. should I use composite index on city,State & Country
  3. should I use composite index on country, state & city

b) If I know that person with ID 1 to 100 are in Nagpur, for such case

  1. should I create index on ID field
  2. should I create index on City field

If you will suggest to create index on id field that reason will be

  1. Due to it is a bigint and bigint occupy only 4 bytes
  2. Id field is unique so it has more selectivity, so even if output of both query will be same Index on Id will give faster respnose.
  • 4
    Normalize your data prior of thinking of indexes
    – Oleg Dok
    Jan 18, 2012 at 11:33
  • bigint is 8 bytes rather than 4 BTW Jan 18, 2012 at 11:36
  • ....And that's 5 fields but you have two 2 fields...
    – JNK
    Jan 18, 2012 at 12:51

1 Answer 1


As Oleg mentions, you really should normalize. For your scenarios, though:

A - I would use an index on City INCLUDE ([name]) - you don't need to sort by name as well but you do want that field at the leaf level of the index.

B - I'm not sure what you are asking here. If you have an auto-incrementing ID then that should be your clustered index.

There's a lot of reasons to use an auto-incrementing ID as your clustered key. I won't go in depth on these (there are a ton of resources, and Kim Tripp is a really good place to start):

  • Will only insert new records at the end of the index
  • Narrow (since it will be a part of every other index)
  • Unique (keeps it narrow since non-unique indexes need a "uniqueifier" additional int added)
  • Non-nullable
  • But in this case if id field is not incremental or primary key than what should I do Jan 18, 2012 at 13:22
  • 1
    Why do you even have an id field then? If you're not normalizing, and not using it as a PK, then what purpose does it serve?
    – JNK
    Jan 18, 2012 at 13:34
  • It is not like that I not used Id field as primary key in my table. I just want to clear my doubts that if there is any other field as primary field than whom should i use in index Jan 19, 2012 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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