Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Standard/best practice for indexing foreign keys?

Author  Topic 

waveform
Yak Posting Veteran

93 Posts

Posted - 2014-07-16 : 10:13:47
Hi, just wondering what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools]             [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])


What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)

Or

CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

Or something else? In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

Many Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-16 : 10:45:19
In this specific example, assuming StudentId is the clustering key, it wouldn't make any difference whether you create the index on SchoolId or you create it on SchoolId+StudentId. This is because the clustering key will be stored as part of the non-clustered index even if you don't include it. If you do include it, it won't be duplicated.
Go to Top of Page

waveform
Yak Posting Veteran

93 Posts

Posted - 2014-07-16 : 11:11:07
Ah cool, I didn't know that. Thanks very much!

If I want to run a *sorted* joined query like:

SELECT sc.SchoolId, st.Name
FROM Students st INNER JOIN Schools sc ON st.SchoolId = sc.SchoolId
ORDER BY sc.SchoolId, st.Name


The fitting index would, I assume, be:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, Name)

Would that also serve as a useful FK index? Or is it best to also index the FK column separately?

If there is indeed that specific FK index on [Student].SchoolId, do I only need to index the column (Name) for the above query to be efficient? Or should it still be (SchoolId,Name)?

Many thanks!
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-17 : 08:10:58
For such a question, try it with and without the extra index. Look at the execution plans for both. If you see a Sort operation on the one without the index but no Sort on the one with the index, then the index would help that particular query (though it will slow down inserts and deletes and maybe updates due to extra index maintenance). If you see a Sort operation on both versions, the index doesn't make a difference.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-17 : 12:43:50
quote:
Originally posted by gbritton

<snip>though it will slow down inserts and deletes and maybe updates due to extra index maintenance
In general this is true. The flip-side is that if Students.SchoolID is not indexed, it can have a major performance hit on Delete's from the Schools table (assuming a foreign key exists). That may not be an issue in this case, but something the OP should also understand/be aware of when considering an index strategy.
Go to Top of Page
   

- Advertisement -