SQL Server Performance

Proper Index

Discussion in 'Performance Tuning for DBAs' started by ravi0207, Oct 13, 2009.

  1. ravi0207 New Member

    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
  2. atulmar New Member

    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?
  3. satya Moderator

    I believe this souds like a classroom questions, all you need is 2 indexes one is clustered and another of composible with country & location.
  4. ravi0207 New Member

    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
  5. vikasrana_dba New Member

    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.
  6. ravi0207 New Member

    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
  7. vikasrana_dba New Member

    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
  8. ravi0207 New Member

    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

Share This Page