SQL Server Performance

Combined Index - Performance

Discussion in 'ALL SQL SERVER QUESTIONS' started by GabrielAndres, Dec 20, 2013.

  1. GabrielAndres New Member

    Hi,
    I'm running SQL 2000 and I have a table with an index1 on ID1 and ID2, in that order.
    Select count(distinct(id)) from table returns 1
    and Select count(distinct(id)) from table has all the values of the table.
    The querys to that table uses "... where id1= XX and id2 = XX "
    Could it make any performance improvement if I switch the order of the fields of index1 ?
    Or is it indifferent because id1 has only 1 value?
    Thanks for your time!
  2. davidfarr Member

    I'm not completely understanding your query. You sound a bit contradictory.
    It sounds like you're saying that you have two table columns [ID1] and [ID2] and one composite index named [index1] on both those columns. If your columns are named ID1 and ID2 then how is a query such as "Select count(distinct(id)) from table" possible ? Where did the third [ID] column name come from ?

    If it helps then I can say that, to my knowledge, the order of the columns included in a composite index are irrelevant, they are not evaluated in any specific order and I do not suspect any change in performance by switching them around.
  3. GabrielAndres New Member

    Sorry, the Query should have been:

    Select count(distinct(id1)) from table returns 1
    and Select count(distinct(id2)) from table has all the values of the table.
    The querys to that table uses "... where id1= XX and id2 = XX "

    I got the answer from here:
    http://stackoverflow.com/questions/20706178/sql-server-multiple-index-order-optimization
    where it says that the index order is important but in my exact case it wouldn't make any big difference

Share This Page