How to sort relational table without surrogate PK by insertion order?

Question

Say I have a JOB and COMPANY table and a third JOB_COMPANY table that relates the two in a many-to-many cardinality.

I've learnt that it's an anti-pattern for the JOB_COMPANY to have a surrogate id PK. The PK should probably be a composite (job_id,company_id).

In this case, following the best practice, how would I sort the JOB_COMPANY by insertion order (and DESCENDING also)? Would this case justify a surrogate id PK?

Answer 1

So its not the end of the world having a surrogate key as a primary key, as long as it isn't a clustered primary key. You want your clustered index to be a composite key in majority of cases as it will usually be how your table is accessed most of the time so you will see performance gains. Unless however you have a table where the data is only accessed by ID, in that case the only choice for the clustered Primary key is ID. What you could have is:

Primary Key NonClustered - ID
Unique Clustered Index - job_id,company_id
OR
Primary Key Clustered - job_id,company_id
Unique NonClustered Index - ID

That way under the hood 2 indexes are created to be used. So you can seek to your data with both of the below predicates

WHERE ID = 
OR
WHERE job_id = 
AND company_id =