Again, as I stated, it took only 1 more byte to store YYYY vs YY.  Dates were stored as numeric and not char, also to save space.  YYMMDD took 4...

Regarding the fact_OHADiagnosis table:

1) should likely be clustered / partitioned by ( OHADiag_id, OHADiagnosisOrder ). You can have a table that can quickly translate FileYear to beginning and ending OHADiag_id....

If you want better performance, I think we do need actual table definitions, esp. including the indexes.  It appears the diagnosis id is what's critical.  You should likely be partitioning...

It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where...

The good-ole standard tally table should handle this nicely.  I default to end of calendar year for end date; naturally change that if/as you need to.

DECLARE @end_date...

Assuming you consider only whole months, truncating any remaining days, then:

declare @age varchar(6)
declare @dob datetime

set @dob = '2015-07-31 00:00:00.000'
select @age = cast(months_old / 12 as varchar(3)) +...

It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300 baud...

GUIDs are horrendous for clustered indexes.  They fragment like crazy.  And a guid is always a single key lookup, not a range (at least in my experience), thus a non-clus...

also state that your CHECK for the start date being less than or equal to the end data will fail if the end date is not...

I believe all you need to do is specify the proper PARTITION BY clause in the code (new code is below).  The code originally comes from Itzik Ben-Gan (I believe),...

Recompile forces SQL to recreate the query plan, even if one is already available in memory.

That process may require updating stats if one of the tables in the query has...

That alone might have made me skip buying the product!

Fillfactor also depends on...

CREATE UNIQUE INDEX UK_IDX_TLOG ON TLOG_TB ( TXN_AUTH_ID ) WHERE MSG_TYPE = '200';

It can not use the run length as a parameter, because the window specification requires a literal value.

But for every given run length a solution exists using only six...

>> I want to find runs of at least three 1s, and return the result below. The 0s would be any test_values that are not 1. <<

Since SQL does...