Almost certainly not a factor in case statement/null logic, but...

varchar Column lengths are suspicious -- all varchar(50) in OptionsetMetadata  & varchar(255) in account. Are those really the column lengths used?...

deleted

Yes, post DDL & sample data.

How to post code problems

What data types are account.ts_ukrow & OptionsetMetadata.ts_ukrow? What data types are account.ts_primarysecondaryfocus & OptionsetMetadata.ts_primarysecondaryfocus ?

The CASE statements wrapping ISNULL() using empty...

Did you upgrade to a new version? If so, update statistics.  What version did you move from? to? (presumably one of them is 2017 since you're here)... If you moved...

Given how many times this identical question has turned up both here and on reddit, this has the earmarks of spam.

"AppVManagement"  & "AppVReporting" appear to be for Microsoft Application Virtualization: https://docs.microsoft.com/en-us/windows/application-management/app-v/appv-for-windows, https://docs.microsoft.com/en-us/windows/application-management/app-v/appv-deploy-appv-databases-with-sql-scripts

ReportServer...

Does it work?

Yes, that is using aliases everywhere the tables are referenced.

The syntax is incorrect because you have aliased both tables used in the query, but then still reference the original table names everywhere except in the join between sn and...

Dealing just w/ the question of updating, and not recovering space:

If you are updating all rows, can't you just drop the column and re-add (wondering if/why you still need the...

https://github.com/OpenQueryStore/OpenQueryStore

https://dbafromthecold.com/2017/08/19/query-store-functionality-in-earlier-versions-of-sql-server/

(Haven't tried it, not endorsing -- just stumbled across this recently)

You will probably want to use LEAD windowing function. -- e.g.,

DATEADD(day,-1,LEAD(EffectiveDate ) OVER (PARTITION BY PartNum ORDER BY EffectiveDate))

-- Assumes RevisionNum is correctly determined in order...

Why do you want to use dynamic SQL? Why not a procedure specific for each table? How many tables are you archiving?

You can't make either a procedure or a job...

Deleted

I see a fixed font and formatting tools have disappeared completely

But it seems like MSSQL is doing something quite different

What specifically are you seeing for input vs. output?

If you are trying to connect the trace file which SQL Server is currently writing to, you will get "Access is denied" because the file is locked.

Erland Sommarskog, SQL Server...