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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HCL
  • 36,053
  • 27
  • 163
  • 213

1 Answers1

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.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 1
    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 Jan 17 '14 at 14:29
  • 2
    @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. – Joe Stefanelli Jan 17 '14 at 16:38
  • Thanks for reply joe. That's where I'm confused. If they are just two ints in a table, so why will it use an index if I say "WHERE ParentID = 1 and not "Where ChildID = 1". Is there some kind of ordinality to the columns in an index? For example of I defined composite as (ChildID,ParentID) would queries on ChildID use it and ParentID's not? – mark1234 Jan 17 '14 at 21:24
  • 1
    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. – mark1234 Jan 17 '14 at 21:38
  • 1
    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? – mark1234 Jan 17 '14 at 21:49
  • 4
    This answer right here http://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! – carlosrafaelgn Jun 12 '15 at 15:35
  • @mark1234 I would go for what execution plan is saying not people :-) – Mariusz Jan 20 '16 at 10:33