OK, if you set the fillfactor to 51 and rebuild you will have far fewer splits, perhaps none, because you've already at least doubled the size of the table.  Any...

You should be able to in this case because whether the called proc inserts into a table or returns the result set directly will be controlled by a new parameter...

You're very welcome.

It would help everyone if you marked it as the "Answer".  That way future readers know it's been resolved and they don't need to spend time on it...

It is very possible you need both indexes.  However, developers typically don't really know.  You need to check the index usage stats, as Anthony stated, to know for sure.

While you're...

1. If the varchar columns repeat values frequently, encode the varchar(50) columns as ints, using a separate encoding/lookup table.  This would be, by far, the biggest performance improvement.

2. Page compress...

• This reply was modified 5 years, 4 months ago by ScottPletcher. Reason: Added clarification for bulk inserts if a trigger is used

There's almost always an alternative in coding.  If you don't want the CROSS APPLY, you can replace "dash_2" everywhere it appears in the SELECT with "CHARINDEX('-', string, PATINDEX('%-----%', string) +...

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