SQL Server Performance

usage of index order

Discussion in 'Performance Tuning for DBAs' started by geeth_rajesh, Aug 18, 2005.

  1. geeth_rajesh New Member

    Hi friends..

    My query is

    select * from x

    In table x
    create clustered index c_1 on x(no,name,sal)

    now when i query " select * from x where sal > 1000 "

    The query uses the index."C_1".

    when I create a similar table like "X" and create a non clustered index c_2 on the same set of columns.

    create index c_2 on zz(no,name,sal)

    now when i query " select * from zz where sal > 1000 "

    Still it uses the index "C_2"

    My understanding about the usage of indexes is , when a index is present in composite columns, In the WHERE clause the order should be maintained as the index order.

    but the above test contradicts with my understanding.

    Thanks
    Rajesh


    geeth_rajesh, Aug 18, 2005
    #2

  2. ghemant Moderator

    you can use index hint if you wants to be use specific index... moreover you can use sysindexes table for the same to get order of index


    hsGoswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 18, 2005
    #2

  3. satya Moderator

    No the order will be maintained from the WHERE clause as you're querying sal which is the one of field in the index, so it will use the best suitable index. As per the hint execution plan will give you more idea why it is using so.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 18, 2005
    #2

  4. ghemant Moderator

    Hi,<br />as you are using covering index please read an refer below article :<br /><br /><a href='http://www.sql-server-performance.com/covering_indexes.asp' target='_blank' title='http://www.sql-server-performance.com/covering_indexes.asp'<a target="_blank" href=http://www.sql-server-performance.com/covering_indexes.asp>http://www.sql-server-performance.com/covering_indexes.asp</a></a><br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
    ghemant, Aug 18, 2005
    #2

  5. Twan New Member

    Hi ya,

    I think you're asking that you think the order of the results should be in the order of the index chosen...? If so, then no this is no longer the case, you have to specify the order explicitly using the order by clause

    Cheers
    Twan
    Twan, Aug 19, 2005
    #2

  6. FrankKalis Moderator

    While you might observe cases where the resultset seems to be "ordered" according to the clustered index, Twan is absolutely correct on this. There is only one reliable way to get a resultset in the order you expect and that is when you use an ORDER BY.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 19, 2005
    #2

  7. mmarovic Active Member

    What is strange about the query is that non-clustered index where sal is last column is used instead of clustered index or table scan.
    mmarovic, Aug 19, 2005
    #2

  8. geeth_rajesh New Member


    hemanth , Satya..
    How do I use index hints...I am not aware about that.

    hemanth..
    The index which I have mentioned is not an covering index i think ,becaues im not querying all the columns used in the index.


    Twan,Frank...

    I am not talking about the order of the result set.My doubt is on the usage of the index.
    when a coposite index is there , when will the index get used ..

    for e.g lets assume an Index named ==> ind1(a,b,c)

    case 1 : In where clause , if the statement is like WHERE A='X' then index will be used.

    case 2 : In where clause , if the statement is like WHERE B='X' AND C='Y' then WILL THE INDEX "ind1" BE USED.

    case 3 : In where clause , if the statement is like WHERE C='Y' then WILL THE INDEX "ind1" BE USED.

    thanks
    Rajesh.
    geeth_rajesh, Aug 19, 2005
    #2

  9. mmarovic Active Member

    quote:case 2 : In where clause , if the statement is like WHERE B='X' AND C='Y' then WILL THE INDEX "ind1" BE USED.
    if there is no index starting with either b or c column and there are no expressions in select list using columns other then a, b and c, then index mentioned will be used. If conditiones mentioned are not fully met, this index still may be used if there are statistics on b and/or c column depending on cost calculations.
    quote:case 3 : In where clause , if the statement is like WHERE C='Y' then WILL THE INDEX "ind1" BE USED.
    The same logic is applicable for case 3.
    mmarovic, Aug 19, 2005
    #2

  10. FrankKalis Moderator

    Sorry, I've misunderstood you apparently. In addition to what mmarovic already said, see if this helps:
    http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 19, 2005
    #2

  11. satya Moderator

    satya, Aug 19, 2005
    #2

Share This Page