Relatioships and Indexing

Last Post 27 Mar 2008 09:49 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

27 Mar 2008 08:32 AM
Hi everyone.

Simple question here.. just wondering what the established best-performance is to setting up indexes to reflect one-to-many relationships between tables. To take the classic Authors and Books example - many books to 1 author - I want to search my Books table by Author, then by Title. So my query would be:

SELECT a.LastName, a.Initial, b.Title FROM Books b INNER JOIN Authors a ON b.AuthorID = a.AuthorID ORDER BY a.LastName, a.Initial, b.Title

As you can see I want to sort by authors name, then by book title. The way I would currently index the Book table is as follows:

Primary Key: BookID (Incremented Identity)
Secondary Key: AuthorID, Title

My question essentially is: Is that best practice? Or should I have 2 *separate* indexes, one just for AuthorID (the foreign key), and the other just for book Title? How does SQL Server prefer it to be done, to make such JOIN queries most efficient?

Hope I've explained it well enough.. thank for any advice!
New Member
New Member

27 Mar 2008 09:49 AM
Generally makes sense to have indexes on Primary and Foreign keys.

If you are frequently searching on some column, you have to consider the benefit of an index.

Also, if you can cover frequent queries in indexes (satisfied from index), you will get better performance.

The Database Engine Tuning Advisor will help you out with index recommendations.

Kalman Toth

Acceptable Use Policy