Selection of Indexes in SQL Query

Question

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.

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