SELECT * FROM TABLE can't really be optimised, as far as I am aware. It's a full table scan.

(Even though you want all the columns, you should still name them,...

I suggest that you do it using this method (copied from https://sqlperformance.com/2020/09/locking/upsert-anti-pattern):

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE  = @key;
...

MSOLEDB is available:

In the application they would select, from a drop down one of the symbols (<,>,=,!=). I figured I can assigning them letters or numbers, if it is number one...

You'll have to use fully dynamic SQL to make this work.

Building arithmetic expressions like this is generally regarded as an anti-pattern in the T-SQL world. What is the underlying business...

@DesNorton, this sounds like a good solution, well done.

Can you expand a little on what the 'sessions' are?

How should one 'session' know what the other sessions are doing (so that they don't have any overlapping or missed rows))?

How...

In this case outer or cross is same thing - I do tend to use outer apply (left outer) instead of cross apply (inner join)

I have no idea whether this...

Can you expand on what you mean by "but this doesn't produce the desired result"?

Note also that a unique constraint is not quite the same as a unique index.

Frederico .... maybe you're onto something.

Your query has multiple references to ftbid01 which obviously should be to 01, 02, 03, ...

The outer apply could be a cross apply, or do...

I don't see anything that would require this query to be written dynamically. This should work:

UPDATE a
SET fta1 = 'ftbid' + CAST(DAY(b.orderdate) AS VARCHAR(2))
FROM tba a
 ...

The service account for which service, exactly? Or is the same service account running multiple services?

I've done this before, using a Script Component source.

You'll find articles online from others who have done this before. Here is an example.

Sorry, I thought you were referring to sys.objects and sys.key_constraints.

TVFs obviously don't have PKs. Not sure why it...

They're Views, not TVFs. Views don't have PKs.