Be sure to cluster the table on ( MyDate, $IDENTITY ).  The $IDENTITY just insures that the key is unique.  That will make the deletes very efficient.

Yes, it is very important here, to ensure that the nonclus index key is unique.

And, besides, all clustered index key column(s) will always be added to every nonclus index whether...

I changed to using descriptive variable names.  And I added some debugging displays to help you work through any issues.  The sample number you provided is failing the test, but...

Make the nonclus index UNIQUE yourself, don't force SQL to do it for you.  This is important for best performance.

And, yes, you should include the DateDeleted in the index.  I...

I don't think the current method is a bad approach.

You can use a filtered index to identify the rows needing recalculated.

IF the calcs were easy, I guess you could put...

Are you genuinely on SQL 2016 or higher?

If so, I think CHOOSE should save you a lot of coding here.

No.  SQL doesn't really care which proc is using the index, and even trying to capture that kind of data about index usage would be huge overhead.  Indexes need tuned...

Do you pre-restore the full backup each time to speed up the next restore? (Assuming you have the disk space available -- and if not, you can get the space...

@@TRANCOUNT itself is fine, I think.  Does UPDATE increment and decrement @@TRANCOUNT as it processes?!

CREATE TABLE #foo (i INT NOT NULL);
INSERT INTO #foo(i) VALUES (0);
SET NOCOUNT ON;

SELECT...

How large is the log file?  The disk area for a log file must be preformatted, whereas data files do not (assuming you have IFI activated, which is standard now).

Do...

You would do that with view(s).  The ENCRYPT / DECRYPT would be only within the view(s).

The users don't have to query anything different, as you can make the original table...

You're welcome!  I'm glad it helped.  Interesting q to play with.

Maybe something like this?:

SELECT Item, Date1, Date2, Date3
FROM @headerTable
UNION ALL
SELECT
    Item,
    CAST(MAX(CASE WHEN week# = 1 THEN Quantity END) AS varchar(10)),
...

Yeah, this can be definitely done in a standard SELECT.

Do you want to show only the weeks that had a match or do you want to show all 52 weeks,...

I'm confused about a couple of things.

First, why do you only want to INSERT "field3" (really column3)?

There's no link or association to #FieldValues.  Surely there must be some columns missing...