Why does primary key order matter?

composite-primary-key entity-framework sql-server

Question

I recently set up a class in an EntityFramework project which designated a couple of its members as a composite key.

However, when it came to time to create the database from this it gave the error

Unable to determine composite primary key ordering for type 'NNNNN'. Use the ColumnAttribute or the HasKey method to specify an order for composite primary keys.

I know how to solve the problem, but I was just wondering why it cares about the order. Isn't a composite primary key just a set of columns on which to key the records, without any particular ordering necessary?

1
7
5/23/2013 11:53:16 AM

Accepted Answer

It matters because the order of primary keys matter in the database. Since primary keys are (usually) clustered, and always indices, whether the key is ordered as First Name, Last Name, SSN or SSN, Last Name, First Name, makes a huge difference. If I only have the SSN when I'm querying the table, and the PK is the only index, I'll see GREAT performance with the latter, and a full table scan with the former.

9
5/23/2013 12:10:59 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow