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...

Don't drop the existing table.

Create a new, test (dummy) Quality table UNIQUEly CLUSTERED on ( Quality_Date, Quality_ID ).

CREATE TABLE dbo.Quality_Test ( ... )

CREATE UNIQUE CLUSTERED INDEX QUALITY_Test__CL ON dbo.Quality_Test (...

And again -- and for the last time, so as not to upset everyone --  you likely need to re-cluster the Quality table to get even more performance gains, and...

What almost always gives the best payback is to first do a review of all indexes on the table, particularly on the clustered indexes.  Until you have the best clus...

Because...

According to your sample data and results, the "Answer" you marked is not correct.  For example, you stated that '10000' should return a 1 -- "Anything between 00000...

Hopefully this should improve things.  As I noted before, it's very likely that re-clustering the Quality table would give you even more performance gains, as it would prevent a full...