Sql-server – Selection of Indexes in SQL Query

indexsql server

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.

Best Answer

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
Related Question