Perhaps, but I would really expect SQL to generate effectively the same query plan for either code for something this straight forward.

Your code is not really consistent, but here's my best guess:

UPDATE pt 
SET LastModifiedDateTime = @dt
FROM dbo.PlayersTypes AS pt
INNER JOIN @TableTypePlayersRestrictions AS prt ON prt.PlayersRestrictionID = pt.PlayersRestrictionID

I'd try to use differential backups, assuming you have a large amount of free disk space in qa, or could get it easily.

When you do a full backup in prod...

SELECT 
    [freetext],
    CASE WHEN FC_start = 0 THEN '' ELSE RTRIM(SUBSTRING([freetext], FC_start, FC_length)) END AS value
FROM dbo.testdata
CROSS APPLY (
 ...

If you can provide actual sample data -- CREATE TABLE and INSERT statement(s) -- I can provide code to give you the results you want.

No, sorry, it's a custom function.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.DelimitedSplit8K (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
RETURNS...

DelimitedSplit8K (assuming the column is varchar(8000) or fewer bytes)

What chars are in provPhoneMain and provPhoneFax?  We don't have your data, so we have no idea what's in those columns.

folks

I can query all transactions for a value B700 that has 9799 records in year 2023 in 2-3 seconds

using the same application, i query C900 in year 2023 and...

As I understand it, some volumes now have more than one read-head so that even on the same volume you could theoretically get better performance (but, note, I am NOT...

If you're going to ORDER the rows going into the temp table, you might as well have SQL create a clustered index based on that order so the final should...

And possibly likewise for SYN.Table2:

CREATE NONCLUSTERED INDEX [table2__IX_col2] ON SYN.table2 (col2, col1) ;

Don't see a plan, but you could try this, in case SQL's having to do a full scan of SYN.table1 now:

CREATE NONCLUSTERED INDEX [table1__IX_col2] ON SYN.table1 (col2, col1) INCLUDE (ProgramType);

Hope it helps.  Btw, the first full backup (step (1)) isn't technically required, so you can skip it.  The last full backup (step (5)), as noted, is absolutely required.

I guess we should verify first that nothing else is preventing the log from being cleared.  Run this statement and check the result: the best result to see is "Nothing":

SELECT...