I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My )...

We need to see the index definitions as well.  It seems to me that the index definitions would need to be different to produce plans and stats that drastically different.

I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My ) 
 ...

A better option overall is to just have the last history row id stored in the header table (yes, denormalize it).

A trigger on the PERSON_HISTORY table can do that very...

Hmm, I remember a WHERE condition on the OccurredDate; maybe I'm mixing this query up with another one.

At any rate, one last time, as I stated earlier:

the history table should...

First, the history table should be clustered leading on OccurredDate.  That's generally true for log tables.  [Forget the stupid myth that "by default, every table should be clustered...

That's not technically required on the _Test version...

I think it's just this specific thing that bothers me: the first digit of a zip code is meaningless in a business context.

That's why I just can't see myself building...

Yes, delay, which will hopefully prevent.  For example, rebuild that index every 3 days, or however many days between when it gets too fragmented.  If it still fragments too quickly,...

Who t.h. is "Scoot", Jeft?

Hmm, so you allow ad-hoc tables with NO design process or thought, i.e. no data modeling, to be created and deployed?  Interesting.

At any rate, how do...

Actually they got the structure right for this table: the clustered index is what's critical, and it's not fragmented (presumably).

To help correct the PK but NONCLUSTERED index -- which again...

And I still say you need business input before deciding on what to put in any table you create.  I don't think you can properly model the data until then,...

First, the history table should be clustered leading on OccurredDate.  That's generally true for log tables.  [Forget the stupid myth that "by default, every table should be clustered on IDENTITY"....

Again, I'm still sticking with code, such as a function, for this rather than a physical table until I see an actual need for a table.  The first digit alone...

But any lookup table does NOT need to contain a list of all zip codes as originally stated.  If the "slab" is what is significant, the lookup only needs to...