SQL Server Performance

Index column order

Discussion in 'T-SQL Performance Tuning for Developers' started by flachance, Mar 28, 2003.

  1. flachance New Member

    I was wondering how SQL Server decides to use an index. My understanding is that the order of columns in an index is hugely important. I've ran query optimizer on some queries and it sometimes indicated that a new index would be helpful. The thing is that I already have indexes on that same table, but the column order is different.

    Is the way the SQL Query is written dictates the order of columns used in the index?

    Thanks,

    Francois
  2. bradmcgehee New Member

    Index order is very important. In the case you describe, you are better off leaving both indexes so that they both can be used by appropriate queries.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. dtipton New Member

    SQL Server stores statistics on only the first column in a multi-column index. If the first column has a very low cardinality there is a good chance SQL Server will not use the index.

    Example:

    Say I run a shipping company in California and 99% of my customers are in California. An index on State, City would have a very low cardinality and might not be used by SQL Server. However, an index on City, State would have a much higher cardinality (assuming all of my customers do not reside in the same city) and is much more likely to be used.

    Don

Share This Page