Yep, you are close already.

And, in fact, your existing clus index on the claim line table is preferred IF:

(1) claim numbers are all unique (i.e. they never repeat across clients)

AND

(2)...

How much fragmentation depends on how INSERTs are done.  Singular INSERTs will see more fragmentation than multi-row INSERTs.  Either way, you can mitigate this with partitioning (based on the clustering...

You're on the right general lines, but you don't really need partitioning.  Typically what you need for best overall performance is the best clustered index on all the child tables...

That's not factually correct.  NOLOCK prevents having to take, and release, that shared...

RETURN works only within a stored proc.

You could switch to a GOTO:

IF EXISTS(SELECT 1 FROM dbo._proc_control_table WHERE Proc_Name = 'sp_Populate_NEW_NEW')
BEGIN
RAISERROR('This proc is already running, please wait for...

Is the code inside a stored proc (as stated in original post)?

The RETURN statement will exit the proc.  The -1 is a return code that can be checked by code calling the proc to see if an error occurred.

Easiest way would likely be at the start of the proc, write a row to a control table.  At the end of the proc, delete that row.

If the proc tries...

Nvm, just saw the follow up comment from OP.

You would want a nonclustered index on dbo.OrderProducts ( OrderID, ProductID ) to support this query.

--using variables to make the code easier to use and maintain
--may specify...

SELECT ca1.*
FROM TableA a
CROSS APPLY (
    SELECT A.Extension, A.Name, A.UsageCount, 'TableB' AS [Used By], B.VDN AS [Usage Data]
    FROM TableB B
...

Hmm.  I'm not sure how we are supposed to debug this not seeing any of the SQL used to create the table.

Can you at least post the DDL that you...

...

SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
     CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
     CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
    ...

SELECT 
    COALESCE(ABEE.Operator_Ref, ABG.Operator_Ref, GMBH.Operator_Ref) AS Operator_Ref, 
    CA1.[DateTime_Started]

FROM (
    SELECT Operator_Ref, 
     ...

I still don't get your insistence on the guid being so always great as the clustering key for a table.

As a stand-alone table, maybe, IF all the rows...