CODEWITHSUNDEEP
sqlsql-serverdatabase
RonApple1996
RonApple1996

Reputation: 289

When to add an index to a SQL table?

I have a table called Foo that has two columns A and B. Both of these columns are foreign keys to two other tables. These columns together make up the primary key, and it has a clustered index for this primary key by default.

My question is... does it benefit me at all to have another non-clustered index for B? Or is this unnecessary?

Upvotes: 1

Views: 372

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415705

First, some background, to make sure we're on the same page.

It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:

Bar(A) <=> Foo(A,B) <=> Baz(B)

If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).

For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.

Now to answer the question.

The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.

But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.

Upvotes: 2

PhilS
PhilS

Reputation: 624

Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.

If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.

Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.

btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.

Upvotes: 0

Related Questions