"This table has the BusinessEntityID as the PK"

Well that would have been nice to know!

And we can obviously manage more than one "table needing a default row" in the new table - one column per such table.

Does anybody know of any drawbacks...

Thanks for your replies. I should have made it clearer in my original post - we need to be able to select one row as the default, not one row per...

Yes, I'd noticed that as per the original post

The 8 second / 44 second difference arises from the Filter. (See attachments.)

Does anyone have any idea why actual elapsed time would...

Thanks kaj. That was one of my first thoughts too, but the connection properties are identical (both from SSMS), as are the execution plans apart from the values of time...

Thanks Johan, but I would like to know what's going on with the original code to satisfy my own curiosity if nothing else.

Execution plans now attached as a zip file.

• This reply was modified 2 years, 6 months ago by  julian.fletcher.

I'm probably going to fix this particular instance by reworking that terrible scalar GenerateCostCode function. However, I would like to know what's going on to satisfy my own curiosity if...

• This reply was modified 2 years, 6 months ago by  julian.fletcher. Reason: Typo corrected

We have about 160 instances of "WHERE (1 = 1)" or "ON (1 = 1)" in our code base and 950 of "CROSS JOIN".

The code base has about 2.6m lines...

You get the same results with "CROSS JOIN #SubDetails SUB".

The code I posted is an abstraction / summary of some production code we were investigating recently. I'm just interested to...

Easy one for me - it's permanently on for our databases as some bits of code grind to a halt with the new CE.

Scott, thanks for your suggestions. You're obviously a fan of clustered indexes! I've put clustered indexes on the columns being used in the query and it doesn't seem to have...

Thanks for the suggestion Jeffrey. Doesn't seem to make that much difference - see attached estimated execution plans (original on left, new on right).

While either of those are running, there's...

• This reply was modified 4 years, 1 month ago by  julian.fletcher.

Thanks Alan. I was very excited to try your suggestions - particularly the OPTIMIZE FOR one - but unfortunately, neither seem to help.

So for the time being, I'm having to...

T_DeliveryTimetableItemResolvedCost has its clustered index on the DeliveryTimetableItemResolvedCostID column, not on DeliveryTimetableItemID.

Unfortunately, we have a bit of a standard, which is that tables called "Thing" have a clustered index on...