This is very interesting, can you post both execution plans please?
😎
Question, are the sql server configurations the same for both?

I've seen huge performance improvements going...

Straight forward if you use the Window functions
😎

USE TEEST;
GO
SET NOCOUNT ON
Declare @test-2 TABLE
(
FullName varchar(20),
NewType...

You can simplify this query, no need to use cross apply or a CTE.
😎

USE TEEST;
GO
SET NOCOUNT ON

Declare @test-2 TABLE...

Only one comment on this one, "RE-DESIGN"
😎

There are too mane anti-patterns and bad practices in the code for even starting to comment on it. Things you...

I recommend using a straight self join rather than the CTE as the CTE will have both sort operators and a hash match in the plan.
😎

Self...