Selection of Indexes in SQL Query 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' 


should I use index only on column city
should I use composite index on city,State &amp; Country
should I use composite index on country, state &amp; city


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


should I create index on ID field
should I create index on City field


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


Due to it is a bigint and bigint occupy only 4 bytes
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.
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