SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Standard/best practice for indexing foreign keys?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waveform
Yak Posting Veteran

Australia
89 Posts

Posted - 07/16/2014 :  10:13:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 07/16/2014 :  10:45:19  Show Profile  Reply with Quote
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

Australia
89 Posts

Posted - 07/16/2014 :  11:11:07  Show Profile  Reply with Quote
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!

Edited by - waveform on 07/16/2014 11:18:19
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1089 Posts

Posted - 07/17/2014 :  08:10:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/17/2014 :  12:43:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000