My main advice would also be not to modify the table structure unless you really need to (I deliberately put the code in comments that (if you wanted to) changed...

It's nice that there's already an index on dDateIn. I would suggest something like this:

--Setup

(A1) Create a new table, with a usable clustered index. Add data compression to the table...

If the table is just a heap (you said there's no PK, but that doesn't necessarily mean there's no clustered index), as you know, you'll have to rewrite the whole...

I think if you do the UPDATE and lookup all in one go, you won't have dups nor deadlocks:

...

UPDATE dbo.Reference_Numbers
SET @NextReferenceNo = NexNextReferenceNo = NextReferenceNo + 1...

• This reply was modified 4 months ago by  ScottPletcher.

No.  If they're sleeping, and don't have any active tasks, they are not using up CPU.  They are taking a small amount of memory.

I've generally found MERGE to be less efficient than UPSERT.  Others maybe not.

You could generate the necessary WHERE clauses and CASE clauses to conditionally UPDATE 200 columns.

Because * SELECTs all columns, which would include hMy.  Try this instead:

SELECT hMy, *
FROM dbo.WF_HEADER
WHERE 1=1
ORDER BY 1 --<<--

For one thing, don't force only LOOP joins, YIKES!

To tell you anything else, would need much more details on the tables and parameters.

The data seemed inconsistent / "impossible" to me.  How can the left-most 50 bytes be '3006' when that doesn't appear in the original value at all??

Don't do online, do offline.  That should be ok if you have scheduled down time anyway.

Otherwise, hard to say without more details.  As stated above, you may not really need...

SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB' 
WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
ELSE 'Replenishable' END AS 'Replentype'
FROM dbo.tablename t1
WHERE...

Select TBL1.caseid, TBL1.anotherid, TBL2.locked

From atable1 AS TBL1

JOIN atable2 AS TBL2 ON atable2.caseid = atable1.caseid

where TBL1.date Between CAST('2021-03-19' as Date) and CAST('2023-03-30' as Date)

and not exists(select * from atable2 AS TBL2 ...

You just need a NOT EXISTS in the original query, but can't be coded accurately without knowing which columns are in which table(s).

That query can't run, because you didn't specify which "caseid" in the SELECT.

We have NO way to know which column(s) are in which tables.  You need to alias EVERY column...

Assuming that AVALUE is based on the ID value (that is, the same ID always has the same AVALUE), then you can leave AVALUE out of the GROUP BY.  If...