column order in an index | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

column order in an index

Hi, I am looking at a database which has one of the columns present in almost all tables and that column is also part of the PKs and hence is part of the indexes as well. example:
emp_table
———-
cntry_id
emp_id
name
.
. dept_table
———
cntry_id
dept_id
dept_name
.
. Clearly cntry_id & emp_id will be the combination PK for emp_table and similar will be the case for dept_table. So the index on these tables are having columns in this order : cntry_id, emp_id and so on. But the current implementation allows only one value for cntry_id, that is, cntry_id is same for all the records in the table. But still it has to be part of the combination PK. Now my question is, since value for cntry_id is same for all the records, will changing the order of the index improve performance (index in the order: emp_id,cntry_id). Best regards,
Could this be one of a series of databases, with some sort of replication between them, where the Country_Id identifies the "database of origin"? If there really is just one value for CountryId, and you cannot drop this column from the composite index, then it MUST be the first. Any other position will probably ruin response time.
why you need cntry_id for indexes. instead you can have empid and dept id as your Primary keys —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

The product/database is designed to handle only one country, there is no replication done one this, this is absolutely redundant in the current implementation. Now regarding response time, my assumption was that the most unique field in the index should be first so that the records get narrowed down in the first go itself. If we have cntry_id specified first in the composite index and since we have all of the records having the same value, we will not be getting any benefit isnt it? PS: I have deleted the other post.
quote:Originally posted by Adriaan Could this be one of a series of databases, with some sort of replication between them, where the Country_Id identifies the "database of origin"? If there really is just one value for CountryId, and you cannot drop this column from the composite index, then it MUST be the first. Any other position will probably ruin response time.

If you have two columns in the index, then it doesn’t matter. If you have more than two, then country can be either the first column (redundant, but SQL can skip it and start on the second column) or the last column (again redundant, but SQL can ignore it completely). If the countryid is in-between, then it is bad for the column(s) after it.
… uh, that would be true if CountryId had multiple values. As it has only one value, it might as well be in the middle of the composite index.
]]>