CODEWITHSUNDEEP
sql-serversql-server-2005
HCL
HCL

Reputation: 36775

Composite primary key and additional indexes

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?

Upvotes: 15

Views: 6853

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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.

Upvotes: 23

Related Questions