Menu
In SQL Server 2005, I have a table with two columns: parent_id (int) and child id (int). I want make a composite key of them, because I only want exactly one instance per possible combination in the table.
The most search operations will be done on the parent_id field, some on the child_id and only sporadic ones on both fields together.
I have planned to make an index on the parent_id field and maybe also one on the child_id field. Is this meaningful or is SQL Server 2005 capable of using the clustered composite primary key for indexed lookups on only one column (mostly the parent_id) and therefore the index is not necessary/dispensable?
562
Primary keys that have more than one column are always automatically indexed as composite indexes with their columns in the order that they appear in the table definition, not in the order that they are specified in the primary key definition.
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.
For maximum query flexibility, you can create up to 20 global secondary indexes (default quota) and up to 5 local secondary indexes per table.
A primary key that is made by the combination of more than one attribute is known as a composite key. In other words we can say that: Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.
Make the composite primary key (parent_id, child_id) to enforce uniqueness. SQL Server can use the composite for searches on both columns or parent_id only, but it cannot use it for searches on child_id only. If you need it, a separate index on child_id would have to be created.
143
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
