I identified the changes I made to the original code using /*--<<--*/ on/around them.  This code should also perform better.

SELECT
sa.partid AS ALLOC_Part,
sa.quantity,
sa.ordertype,
sa.worksorderid,
sa.reference,
sa.stockvalue,
apm.partdesc,
soi.orderid,
soi.itemnumber,
soi.partid AS SOI_Part,
so.traderid,
t.name,
u.name As SalesRep,
sub.Max_Batchvalue AS...

You could also use Brian Gale's approach and use a proc to generate all the code for you.  Personally I would stick with dynamic SQL, but the other is an...

Columnstore won't gain you much (other than perhaps some disk space) since you are reading all the columns.  Personally, I would just use page compression.

Absolutely agree with analyzing page compression for the table.  It could be a huge help.  You can use:

EXEC sys.sp_estimate_data_compression_savings 'dbo', 'table_name', NULL, NULL, 'PAGE'

As  you noted, the nonclus index is...

I thought it was more complicated than that because a CHAR(13) should also count as a "space" as far as splitting words?!  Perhaps not, I guess it depends on exactly...

IF UPDATE(CreditLimit)

That approach seems preferable to me, unless you really have to use COLUMNS_UPDATED()

If the quirky UPDATE works, can you modify the original table to include the new calculated column?

Let me ask: once a value has been calc'd, would it ever change?  In...

Please provide data in usable format: CREATE TABLE and INSERT statment(s).  That is way more useful to us than a "picture" of data.

Would need to see row counts to better understand the query.

How is the Departments table clustered?  In general, again without knowing any details at this point, DepartmentID first followed by...

If the condition is on a LEF T JOIN, moving it to the WHERE will effectively convert the LEFT JOIN to an INNER JOIN.

I use a table.  (Longtime DBA, so that comes naturally to me.)

Basically, you need a table for the passwords, anyway, right?  Of course the table's encrypted and in a restricted...

SQL Server can maintain statistics that "tell" it how many rows are in given ranges of key data (histogram data).  For hard-coded, SQL can of course then pick a plan...

My function by default returns all matches, but you have options to prioritize fewer rows matching or more rows matching.

I have a function that does that.  It could also return 400 and (either) 600.  You'd have to specify how to pick one over the other.

Make sure statistics for all large tables are updated.

Review query plans to check for potential performance issues.