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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of SQL Server for 1:0..1, FK on PK or PK + unique non-clustered index on FK? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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)



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?

We are here to answer your question about SQL Server for 1:0..1, FK on PK or PK + unique non-clustered index on FK? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji