Rats, I'd hoped it would work too.

If it's keyed lookups, the size of the underlying table won't matter, SQL would still do the lookup, I understand that.

Here's the general structure.  You can gen this out from the sys.columns table so you don't have to write out the code by hand.

SELECT 
  ...

Yes, there will be some limited page splits.  But the reads against the table will be vastly more efficient, even with the splits.

It's certainly possible that [TimeDayId] then [Material_Werksdaten_key] would...

Add an outer query, something like this:

SELECT *, [Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev
FROM (
    SELECT Count(*) AS 'Count of Tranactions',
   ...

You should cluster the table on:

( Material_Werksdaten_key, TimeDayId )

Your join is very confusing.  You need to specify the TimeDayId(s) directly, not use a function on it.

Is TimeDayId one value per...

select Id, CompletedDate, Code 
from (
    select *, row_number() over(partition by id order by completeddate desc) AS row_num
    from #temp_dat1
) as...

Ignore the previous entry, this is much closer:

SELECT 
    MAX(CASE WHEN DS2.ItemNumber = 1 THEN DS2.Item ELSE '' END) AS Id,
   ...

Something like this?!  It's only a start, obviously.

SELECT DS2.*
FROM dbo.DelimitedSplit8K(@text, '|') DS1
CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2

Yep.  You'll need to go thru each "What's New" separately, for:

SQL Server 2012; 2014; and 2016.

Interesting q.  I've been looking into this, the best try I've found so far is to skew the row and page stats so high that SQL will almost certainly (?)...

I...

In the WHEN MATCHED part of the MERGE, specify only the columns you want UPDATEd.

OK, let's make a couple of quick fixes and then do a quick check for a couple of common errors, and see if we can find any bad data after...

COALESCE(partially_NULL_column, joined_table.column, another_column_in_table_thats_never_null)

webrunner:

You'd get orders of magnitude more improvement by changing all tables to make sure they have the best clustered index for overall performance and based on how data is (almost) always...