That is a bit of a nightmare.

Just looking through the plan, I see four different plan affecting implicit converts. I see a missing join predicate error. I also see wildly...

It's really just a question of breaking down the steps, as you have, and then implementing them one at a time.

One step you are missing though is cleaning the data....

Never heard of that one. I went and looked up the shortcuts documented in SSMS. I think it's a custom one that someone put together. At least the current...

You need to look at the logs. There's just not enough information to go on here. Here's a little help.

The example query doesn't have a WHERE clause, so you're moving every single bit of the data. Indexes will help a little bit, but not much. A query that moves...

ISNULL doesn't exist, but COALESCE is there.

Ah, it's asking you to do a LEFT JOIN and then deal with the NULL values to arrive at the value of zero (0) for anyone who doesn't have...

You really can't. A clustered index, columnstore or rowstore, defines data storage. You can't really redefine data storage without completely rebuilding the table. So, build a new clustered columnstore table...

Oh come on.

Thanks though.

Nah, don't apologize. This forum is here to expand knowledge, so please, use it.

I haven't tried troubleshooting the DTA too much, so I'm not sure what you can do there....

First, don't use the tuning advisor. It's actually more than a little bit dangerous in its recommendations.

Second, are you providing an XML input document? Is it that it's too large...

Hard to know what to say here without detail.

Instead of cursors, use set-based queries.

There you go.

You can't restore to another server & try to test it there? I'd argue it's important to get something like this right because messing it up could be painful.

Testing is your friend. Try Phil's suggestion. It's likely going to be faster just based on the simple idea of data movement. Moving 100 million rows through the copy may,...

Do you mean just a simple backup and restore, not data changes at all, no inserts, deletes, nothing like that, and suddenly SOME values are different after the restore?

Nope.

That doesn't...