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.
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!