Steve Hendricks (9/28/2012)
In re: "it's the selectivity of the data that is what the SQL engine uses to determine whether to use an index or not."For SELECTs that would be true but wouldn't the index on the foreign key be used when validating data consistency during INSERTs and UPDATEs?
stating the obvious, a foreign key is used to validate data consistency regardless of an index on it or not.
an index, if selective enough, might be used by the engine to find the data that needs to be selected/inserted/updated/deleted; if it's not selective enough, or doesn't cover enough columns the engine needs to search on, it might be ignored and a different index or a table scan used instead.
so if you were updating a table that has a FK, for example WHERE STATEID= 12 , and there were hundreds of rows with that value, the engine will evaluate the statistics and selectivity to determine which index might be beneficial to the UPDATE statement; lots of rows to be affected , in this example, and you'd probably see a table scan;
in a different query, where you were updating, say the contacts related to a company, the query might be very selective on the FK:
ie WHERE COMPANYID = 4, and there was only two contacts out of hundreds, that index might be used to find and lock just the rows affected.
Lowell