information about combiend indexing

Question

I tried to find a valuable reference about combind indexing
( How does it works?, When should I use it? , Which fields should be combined indexed? )
but I did not find any helpful paper :(

would you please ,if possible  give me a good reference about that?

thanks so much

Answer 1

You can use a composite index, i.e. an index on more than one field, if you frequently need to sort or search on the combination of some fields, and also if you want to ensure that the combination of some fields is unique. One situation where this is frequently used is a many-to-many relationship. An example is the relationship between orders and products in the Northwind sample database. One order can involve several products, and one product can be included in several orders. This is implemented by way of a third table, the Order Details table, that has fields OrderID (to link to the Orders table) and ProductID (to link to the Products table), plus fields that are specific to the combination of an order and a product.

We want the combination of an order and a product to be unique in the Order Details table - it'd be confusing if one order had the same product twice or vice versa. So the Order Details table has a unique index (in fact the Primary Key) on the combination of these fields:

As you can see, the index name occurs only once in the first column of the Indexes window, and there are two fields in the second column.

(If there had been a different index name in the second row of the Index Name column, there would have been two separate indexes)

---

Regards, Hans Vogelaar