September 24, 2008 at 12:23 pm
If there is a Foreign key to a table will that suffice as an index for that column?
EG. I have a FK based on the CustomerID on Temp1 going to Temp2. Would I also need a create an index on Customerid on Temp1?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 24, 2008 at 1:23 pm
Foreign keys are not indexes. If you want an index on the column, you'll have to add one yourself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 2:31 pm
It is a good rule to always have an index on the columns in a FK.
If the FK table is large, deleting a row from a PK table will result in a table scan on the FK table if you have no index. Any query that starts with the PK and gets matching rows form the FK table will cause a scan on the PK table.
September 25, 2008 at 7:05 am
Thanks a lot for the information.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 25, 2008 at 10:11 am
Doesn't creating the FK back it up with an index?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 25, 2008 at 10:37 am
Arthur.Lorenzini (9/25/2008)
Doesn't creating the FK back it up with an index?
No. As I said, foreign keys are not indexes.
Primary keys are enforced by indexes, it's the only way to check and enforce uniqueness. Foreign keys are referential constraints and do not require indexes to enforce, hence indexes are not automatically created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2008 at 7:47 am
Thank you for clearing that up. I appreciate the input.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply