Index column order | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Index column order

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
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
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
]]>