Proper Index

Hi
Below is my table structure
Emp_ID
country
Location
Department
Designation
ReportingTo
My Query is Select * from MyTable where
Country='India' and Location='Mumbai' and
department ='Accounts'
My question is, do i need to create three indexes on
individual fields or create one composite index with all three fields
Thanks

This is not an easy to be answered question. First of all this table structure is not following any normalization technique.
Now answer depends on what kind of queries are coming to your system on this table, you cannot create index for every query type.
Few questions:
Does Emp_Id has index?
Do you get different kind of queries on this table?

Hi Satya
You are right, this is classroom question, i am new to SQL server and just clearing some doubt. I have 4 more queries which depends on individial fileds like
Department, location and country
for example.
select * from table where department='abc'
select * from table where Location='xyz'
My question is does my composite index works for above queries or do i need to create individual indexes for above queries.
Regards
Ravi

Hi Ravi While creating composite index on Department and Location column likeCreate clustered index CI_table on table(department,location)in this case if you query select * from table where department='abc' then Index seek will happened and if you runselect * from table where Location='xyz' then Index scan will happened so you can seethe clear difference.While creating clustered index you have to be careful while mention the column order.

Hi Vikas
Thanks for prompt reply, you mean for below query i have to create new index on field location?
select * from table where Location='xyz'
Regards
Ravi

Hi Ravi,
As Atul already said you can not create index for every query type
But if I go according to your question then yes you have to create one more index on Field Location.
Regards Vikas

Hi Vikas
If i am not wrong SQL identifies index on first fields name, and rest it will ignore. make it more clear i am gving you this example
Select * from Table1 Where Field1="dd" and Field2="Ddd" and Field3="asd"
I have created composite index on Field1+Field2+Field3
My another query is
select * from table1 where field1="some value"
in this case SQL will use previous index , does SQL use previous index for below queries
Select * from table1 where field2="some value"
Select * from table1 where field3="some value"
Select * from table1 where Field2="Some Value" and field3="some value"
Regard
Ravi