The original query's construct, somehow, allowed SS to rely on the statistics alone and not look at the query from a new perspective. The OPTIMIZE hint steered it in the...

Cool stuff, Craig.

I'd like to delve deeper into your observations before I update the statistics thereby destroying this particular scenario. Earlier I did pull FileSummaries into a table variable with...

Of course, I should have attached the OPTIMIZED execution plan, too. Just an oversight, but here it is. This completed in 38 seconds.

The almost 1 trillion rows that it...

So sorry to hear about your loss.

I have been a pilot since 1967 and have lost some friends this way, too. I'll keep looking for details especially from FAA/NTSB....

What happened with the plane crash, Steve?

One can understand that employers would be reluctant to allow someone with whom they have no trust-relationship to work at home. There are a multitude of distractions at home, such...

Oh, I agree, varchar(max) is totally another beast. My point was that an empty 255 wouldn't cause any increase in disc usage compared to a varchar(10), for example.

Some folks...

I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved....

I clicked the correct answer but it still said that I was wrong and cited an option I didn't click.

Then, it is not "than", it is "then".

It's a...

If you want "before" you must use "instead" and then perform the functions you want to do before the update/delete/insert is performed and then perform the update/delete/insert in code. That's...

Pity it was poorly written, had wrong references, skipped steps, and assumed that the reader knows all the missing steps.

Now that's a useful piece of information, although perfectly logical. Thanks for sharing.

Correct. Even if you use Enterprise Manager or Management Studio, it will create a new table, import all the data and attributes, and then drop the old table. No magic...

It IS important in which order columns are specified in a table. Having varchar columns preceding fixed-length columns place an undue burden on the server because of the overhead of...