I still don't understand why an AWS server that costs more per month to rent than the entire cost of buying my laptop new should take 10 times...

You and I have know each other for...

I think you might want this for a recursive CTE:

;with cte as
(
    select * 
      from [#ChannelInfo] 
 ...

When I said "we", I should have said "they".  What...

drop table if exists #temp;

select *
  into #temp
  from (values (1,21.5,1,20.5, cast('20230121' as date)),
           ...

I can't see how you can order the data in that table?

Can you provide how the table looks before and after the update?

Please be specific as to what kind of blocking occurs and how long it actually lasts.

Then, let's see some proof...

Alter FUNCTION fn_TableByPeriod(
@Period...

Did you terminate the previous statement with a semicolon?

Your subquesry:

SELECT MAX([Datetime])
  FROM [mydbtemp]
 WHERE [Tagvalue] = 2
   AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'

returns '2023-02-03 07:00:00.000'

So your query is equivalent to...

Just wondering what is the point of @Period in function fn_TableByPeriod?

SELECT [Datetime] 
FROM [mydbtemp] 
WHERE [Tagvalue] = 1 
AND [Datetime] > (SELECT MAX([Datetime]) 
FROM [mydbtemp] 
WHERE [Tagvalue] = 0 
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05...

The table you are selecting from (my_table) is not the table you have created (mydbtemp). Also the column names are different.

I think you meant to put this as your query:

Serious "chicken dinner" there for performance, Jonathan... here are the stats from a test on a million rows of 16 hex digits (8 bytes)...

 SQL Server...

Serious "chicken dinner" there for performance, Jonathan... here's stats from a test on a million rows of 16 hex digits (8 bytes)...

 SQL Server Execution Times:
...