Your ordering is not guaranteed to be in the OUT ITEM order, since you don't sort by it.

If you have a lot of rows, you'd likely get better performance putting the results (from the cte) into an indexed temp table, but for now, just see if this...

An alternative that allows a previous open paren(s) in the input data:

DROP TABLE IF EXISTS #test2;
CREATE TABLE #test2 ( tran_log_writes nvarchar(4000) NULL );
INSERT #test2 ( tran_log_writes )...

Personally for something this involved / complex, I'd use a custom function that parsed with pre-determination.  That is, the code doesn't look only for specific names (tags) in the data,...

You specify either a partition or a filegroup, not both (if partition, the partitioning will determine the filegroup(s) used).

create index IX_Service_Start_Date ON ivc_cds.CDS_Claim_Header (Service_Start_Date )

WITH (FILLFACTOR = 98, SORT_IN_TEMPDB =...

Try this:

SELECT DISTINCT Lotr.LabOrderUnique,
    STUFF( (SELECT DISTINCT CAST('^' AS varchar(max)) + CAST(lotr1.TestName AS varchar(max))
        FROM dbo.Ncs_conv_IntergyMM_Labordertestresult AS...

You very likely already have a clustered index on the table, on ( ClaimHeaderID, ClaimLineID ).  (That's a vastly better clus key than an identity column, btw.)

You can only have one...

But you're going to have to include the date in the clustering key to do partitioning anyway, you might as well change the clustering first.  And that will be less...

By far the best performance payoff for this is creating the best clustered index on the table, as coded below.  You would likely not even need to partition then.

Based on...

Clustering by the date will solve the performance issues.  Partitioning will aid performance only if you need to rebuild the table, in which case you would only need to rebuild...

That should be fairly easy using tally tables.

The trickier part would be excluding certain letter combinations, meaning of course you have to determine which ones to omit.  For example: 'ASS',...

This should give you some idea.  With that partitioning, you can no longer have ClaimLineID as a pk (or even a stand-alone unique index/constraint).

If you can use data compression, you...

Not at that level.

I believe ONLINE create/rebuild of indexes is not available in standard.

If you're on SQL 2016 on above, data compression is still available.

I would definitely use a conversion table.

Personally I prefer the "A[0-9][0-9]" format.

Here's an alternative, an all-in-one function that directly produces the last_business_days from start_date and end_date params.

If for some reason you needed more than 100 months of results, naturally you'd have...