11

I'm upsizing a Jet database to SQL Server Express 2008 R2 and before doing so, I'm re-evaluating the schema (it was designed in 1997-98, and the guy who designed it (i.e., me) was something of a moron!).

My question is about N:N join tables with a two-column composite key. In Jet, joins on the first column of a two-column composite key will use the composite index, but joins on the second column will not, so in general, in Jet databases with large N:N join tables with reasonably large numbers of records, in addition to the composite index I add a second, non-unique index on the second column.

Is this a good idea in SQL Server?

(Maybe it's not a good idea in Jet?)

Improve this question

3 Answers 3

Reset to default
16

The same rules apply in SQL Server. If you have an index on (ColumnA, ColumnB) a query on only ColumnA or ColumnA and ColumnB together can use the index, but a query on only ColumnB cannot. If there is a need to join on just ColumnB, then you should definitely create the index.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Any downside, other than the obvious index maintenance issues?
Space considerations for storing the index. Additional overhead on Insert/Update/Delete operations. Also consider the cardinality of ColumnB. If it's low cardinality (few unique values), the index may not help as much.
In general, my ColumnB has lower cardinality that ColumnA. Would reversing them and adding the non-duplicate index on the column with the higher cardinality be more efficient?
If B has lower cardinality then your original (ColumnA, ColumnB) index is in the correct order. You just need to decide if an independent index on (ColumnB) would be beneficial in your specific situation.
I'm accepting this as the answer, even though the other answers were quite helpful, too. They were just less direct, and prompted lots of other thoughts.
5

If you have a composite index on columns (A,B) no seek, range scan, sort or aggregate operation can use it for expressions that contain only B. This is true for SQL Server, just as it was true for Jet (Red) drivers (and I think for Jet Blue also). Some other engines might use it in a so called skip scan operation.

So the answer is that you need separate indexes on (B) alone.

Improve this answer

11 Comments

If the table is mostly used with joins on both columns, will it help that SELECT as much as it would in a SELECT using only the 2nd column in a join? My thinking is that the optimizer is going to select first on the side that has an index and then has less to do on the other side, so with both joins it's going to be more efficient than with the join only on the unindexed side. Since I hardly ever use join tables without both joins, maybe it's not going to help much?
If the join is on both columns then the index on (B) alone will likely be ignored. But if you search/join mostly on A and B, sometimes on B alone yet never on A alone then you could consider changing the index to be on (B,A) instead.
As a general rule, the column order in an index should be in increasing selectivity order, unless specific ORDER BY needs to be addressed. Putting low selectivity columns on the leftmost side allows for aggregate and large range queries to still use the index, but be warned that a low selectivity column will also quickly fall into the tipping point trap: sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx. Having low selectivity column (like Type) on leftmost makes most sense on clustered indexes because clustered indexes are always covering, don't have a tipping point.
When I say "join on both columns" I mean functioning as N:N join table, with ColumnA joined to TableA and ColumnB joined to TableB. I don't ever use multi-column join table composite keys as FK in another table, so I never have join on ColumnA+ColumnB to TableC.
There's never an ORDER by on these composite keys -- they are N:N join tables, and the ordering has no meaning (the columns are FK values from the Autonumber surrogate keys of the joined tables).
|
4

To help you more, just a tip, in SQL server using the Managment studio you can evaluate the performance by "Display Estimated execution plan". It shown how the indexs and join works.

Also you can use the DTA (Database Engine Tuning Advisor) for more info and optimization.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.