Vastly better to instead create a clustered index before you load the temp table.

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard...

Very similar, but using an OUTER APPLY because you said you used a LEFT JOIN.  OUTER APPLY is like a "LEFT APPLY" and CROSS APPLY is like an "INNER APPLY".

Don't...

Sorry, I just have to ask: why would logical file name(s_ need to be preserved?  Are they hard-coded in some existing statements?  That seems dangerous, what if files are added...

I would have thought you could just rename the logical names to end up with the same name.

Either: rename the original logical file name before you add the new file...

Hmm, maybe add another file to the filegroup in the new location.

Then remove the existing file from the filegroup; since the file's empty, you should be able to remove it.

Columnstore has its own quirks.  For example, every time you SELECT data from the table you'll read roughly 1M rows.  If your processing (almost) always does that anyway, such as...

I did give what I think is the reason for the recommendation: to be able to get back to the exact db image is something happens after removing TDE.

I get...

columnstore is an alternative to rowstore with page compression.

Typically you get even better compression with columnstore.

As to which is best, that depends on the specific situation with that table.

I'd say...

Do you really care that much?  That is, isn't it easier just to take the log backup.

I think I know the reason: it would be the only way to get...

ONLY if it's actually NEEDED, just because of the overhead to activate it.

I have (many) hundreds of dbs.  I have RCSI on for about a dozen or so of them. ...

When a page gets full, it is compressed(*), there does not have to be a rebuild first.

(*) SQL attempts to compress the page; if there's not enough gain from compression,...

In general, data compression is a huge performance gain.  Partitioning is not a particular performance if the table has its best possible clustering index, i.e. you lookup by the clus...

This statement:

ALTER DATABASE [MYDB] SET ALLOW_SNAPSHOT_ISOLATION ON

causes the db to start snapshot processing -- adding 14 bytes to each row and storing any needed row versions in tempdb -- and...

SELECT
    issuedate, replydate,
CASE WHEN replydate IS NULL AND DATEDIFF(DAY, duedate, GETDATE()) > 0 
         THEN DATEDIFF(DAY,...