Menu
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?)
919
An SQL composite index is an index with an index key of more than 1 column. It is good for covering searches and lookups like WHERE clause and joins. You can create composite indexes using CREATE INDEX or ALTER TABLE. An SQL GUI tool can also be used.
A primary key index is created by default when a table is created with a primary key specified. It will match the primary key in nature, in that it will be a single-column index if the primary key is on a single column and a multi-column composite index if the primary key is a composite primary key.
If you are creating a composite Primary Key, or a composite Clustered Index that is NOT a Primary Key, you are creating a single index that uses both column values as the clustering key. There is only one Clustered Index!
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.
100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
