15

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?

Improve this question

1 Answer 1

Reset to default
23

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.

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

7 Comments

I know this is an old post, but I'm looking at same issue. I don't understand the answer. If I have a composite key on ParentID and ChildID. How do you determine it 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". ParentID and ChildID are identical, so how can it use the composite index to search on one but not the other?
@mark1234: Think about it as a chain. You cannot reach the ChildID level unless you first pass through the ParentID level. That is why indexes on only ChildID cannot use the index. There's no ParentID filter in the query to get you down to that ChildID.
I created a table with ID1 and ID2 and made it the PK, so a composite key and then queried it with ID1 in the where clause then ID2 in the where clause, both gave me a clustered index scan, so does that mean I get (almost) same performance as having a surrogate key and individual indexes on ID1 and ID2 (i.e. both will use the index rather than doing a table scan). I then dropped the PK and created ID1 as the PK (no index on ID2) when I do Select ID2 From Test where ID2 = 1500 it still does a clustered index scan, even though ID2 is no longer indexed. Now I'm totally confused.
Next I added ID3 and created a non clustered index on just that column, so I have clustered on ID1 (PK); Nothing on ID2; and non-clustered on ID3. When I search on ID1 and ID3, I get seeks (what I'd expect). When I search on ID2, I get an index scan on ID3. ID3 isn't anywhere in the query and ID2 is not a column in the index. What gives?
This answer right here stackoverflow.com/a/389545/3569421 was very useful, and helped me to clarify the concept! The important point is: PARENT is the first column in the composite key, whereas CHILD is the second (third, fourth...) column in the composite key!
|

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.