Hi Jeff,

Just as I appreciate your (and all other!) comments here!

I have read that article, and some of the comments (but with over 20 pages of them, I hope you forgive me for not reaching the end ;)). I won't be adding to that, but I will explain here why I would not use that method in production code:

The index hint will tell SQL Server to use that index, but it does not tell it how to use it. I see three major risks:

1) SQL Server might chose to do an unordered index scan, in which pages are read in allocation order instead of in the logical order dictated by the next page/prev page pointers.

2) There is also the possibility of a merry-go-round scan (http://msdn.microsoft.com/en-us/library/ms191475.aspx). The TABLOCKX hint will effectively prevent most of this problem - with the exception of table scans that use NOLOCK or the READ UNCOMMITTED transaction isolation level.

3) If the plan for the update includes parallelism, you'll definitely get incorrect results as well. I understand from the comments to your article that I did read that you tried to repro this yourself. This is a bit unfortunate, for (as far as I know) no current version of SQL Server will ever use parallellism for data modifications; this is so far limited to selecting only. However, with the increase of multi-socket and multi-core servers, I think it's only a matter of time until Microsoft does add the possibility of parallel plans for inserts, updates, and deletes. At that point, your code will break.

I have tried multiple times to break your code using one of the first two risks. I failed. I was unable to force an unordered scan for the update; I never figured out if that's because of something in my desktop, or because SQL Server understands that this update needs an ordered scan. I was also unable to repro a merry go round scan on my desktop - both for this update and for two near-simultaneous select statements, so that is probably due to something in my desktop.

So, I have to applaud you for presenting a very efficient and, based on tests so far, apparently very safe solution. It IS indeeed a very clever and creative solution. BUT ... in production code, "apparently very safe" is not enough. One day, Microsoft might tweak their optimizer a bit, and if that affects the way this update is processed, you'd be SOL.

If an important report for upper management shows incorrect figures, I desperately want to be able to point to Microsoft and say it's *their* fault for building a database that doesn't work as documented. Since the behaviour you're using here is not documented, I'd be unable to do that if stuff starts hitting the fan, and it'll be *my* head on the chop-block. :w00t:

Do you rmember how everyone started creating views with TOP 100 PERCENT and ORDER BY in SQL Server 2000, so that they needed no ORDER BY when selecting from the view? And do you also remember what happened when SQL Server 2005 had an improved optimizer that understood that TOP 100 PERCENT when ordered by something is just everything, so that no ordering was required?