SQL Server for 1:0..1, FK on PK or PK + unique non-clustered index on FK?

Question

The usual way of setting up a 1 : 0..1 relationship is:

PrimaryTable someID:(PK, identity)
ExtendedTable someID:(PK, FK)

If ExtendedTable's data is inserted randomly over time, wouldn't it yield fragmentation on the clustered index of the ExtendedTable? Is this something one should worry about on SQL Server 2012?

If so, would ExtendedTable having its own PK, plus a non-clustered unique index on the FK be any better?

PrimaryTable someID:(PK, identity)
ExtendedTable id (PK, identity), someID (FK, NON-Clustered UNIQUE INDEX)

Thanks.

Answer 1

Short Answer:

I suspect having the entire child table as a single B-Tree is likely to be the most efficient configuration (i.e. PK and FK on the same field, with clustered index).

In other words, your 1st solution looks OK.

Long(er) Answer:

If by "fragmentation" you mean difference between logical and physical order of index pages, you are unlikely to solve it by creating an additional index on (separate) FK field anyway - your queries still need to go through the FK index which is still fragmented (even if the PK index isn't), simply because you execute INSERT statements in the same order as before.

Does the fragmentation actually pose a performance problem in your case? Do you do large range scans through the index and have a small cache and use a mechanical drive (with poor random access time)? Have you actually measured?

(If by "fragmentation" you mean unused space in B-tree nodes, a similar argument as above can be made. Also, see Myth: Indexes Can Degenerate.)

And even if you could somehow solve fragmentation by introducing another index, would that be worth the price of maintaining the additional index, more cache pressure and possibly double-lookup on the secondary index?