Sql-server – What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table

To speed things up, you could try

• Adding a primary key to #CSpec.RowID so you don't scan it every iteration
• Change the CTE to a temp table with suitable PK. See next point too
• Add an index on SourceTable1 to match the CTE WHERE clause: currently the PK will be scanned, meaning all SourceTable1 rows will be scanned every iteration. All 52 million rows
• SourceTable2.MarketID also does not have an index, but I wouldn't worry about this because it is scanned once only (as I understand it)

The query plans here should show a lot of scans because you have poor indexes for the operations you are doing.

Target table indexing appears OK

Another observation: uniqueidentifier and varchar are bad choices for clustered indexes (your PKs here): too wide, not increasing, overhead of collection comparisons at least

Edit, another observation (thanks to @Marian)

Your clustered index is wide generally. Every non-clustered index points to the clustered index, which means a huge NC index too

You could probably achieve the same result by reordering the clustered PK.

I performed the DROP command and it was instantaneous.

I did not attempt to wrap the DROP and ALTER PROC in a transaction however.