Hugo Kornelis (9/3/2010)Please go back to page 4 of this topic. One of my posts on that page includes a very fast algorithm that completely avoids undocumented features. And further down, Jeff even posted an optimized version of that code.

Hugo Kornelis (9/4/2010)My examples on page 11 in this topic ...

Hugo, I have a small request. Instead of posting the page #, could you post the url of the post (in case you're not aware of this, click the post # at the bottom left of the post and up pops a message box with the complete url to the post).

The reason I ask is that the # of posts to display in a page is configurable. If yours is set to 10 per page, then your post on page 4 is between post 31-40. If yours is set to 20, then your post is between 61-80. Mine is set to 50 per page - which is between 151-200.

I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

For me, page 11 would be for posts 550-600, and we're not that high yet.

Hugo Kornelis (9/5/2010)I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Hugo Kornelis (9/5/2010)I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Hugo Kornelis (9/5/2010)Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.

Sadly I fear it is not so. While it is true that the Sequence Project that calculates row numbers must run serially, there is no guarantee that the optimizer will not choose to restart parallelism after that iterator. If the critical Compute Scalar runs in parallel, bad things will happen.

With the current costing model, this is outrageously unlikely in practice because Compute Scalars are barely costed at all (reference 1; reference 2 - both from Conor). This costing arrangement may well change in SQL11.

Hugo Kornelis (9/5/2010)There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul White NZ (9/6/2010)I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul

We can make a small code change which I believe guarantees that that particular quirk is impossible. If we modify one piece of your original code

then I think that the compound case statement which is the RHS of the assignment has to be evaluated at a single row - allowing the optimiser to split the evaluation of a single scalar expression so that parts of it are evaluated using elements from different rows would quite independently of quirky update render the assignment components of a SET clause so ill-determined as to be useless.But of course the optimizer is a law unto itself, according to some, so I could be wrong.edit: had an extra [/quote] in there which jumbled thiongs.

Paul White NZ (9/6/2010)Very nice, Tom! That would indeed be sufficient to avoid the issue.

Hey guys - don't take this wrong. But I just love it when someone says to a guru "hey, what about this?" and the guru does a "OMG - you're absolutely right" type of thing. Just another example of not seeing the forest for the trees, even for the guru (who might have seen most of it, but missed something). Plus, I also love how the community works together to make things better.

Paul White NZ (9/3/2010)...my strong preference is for INDEX(1) over INDEX(0) for performance reasons: QO may choose to introduce an explicit sort with INDEX(0) since the TABLOCK hint allows for an IAM-ordered scan.

I need to correct myself here - or at least clarify - INDEX(1) does not guarantee order in the case of scans (it does for seeks of course).

Any index scan can be performed in allocation order (rather than index order) if TABLOCK is specified, or if the effective isolation level is READ UNCOMMITTED.

There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).

Hi Jeff, Records aren't always stored physically ordered on a data page. Most of the time they are physically ordered correctly, but the actual order comes from the slot array. I was curious to see whether a different physical order breaks the quirky update.

Paul White NZ (9/7/2010)There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).