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?
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 =
