Hugo Kornelis (11/10/2009)
Hi Jeff,Thanks for doing a rewrite of this article.
Just a few comments before I start really reading the article:
1) Being one of the people who tried to grill you over a small fire for your recommendation to use what you call quirky update, I feel olbliged to point out that, no matter how many tests you run succesfully and how many rules you add to rule out problem cases, using undocumented features will always remain a huge risk. Of course, you are right that you can't prove reliability of SELECT either. But if I ever encounter some weird case where SELECT does not behave as expected and as documented, I can file a bug with Microsoft and have it fixed. If I encounter a case where the quirky update fails to work, I can try to file a bug but I should expect the MS guys to do the told-you-so-dance.
2) The code in figure 2 will blow up on any server with a case sensitive collation. You should change "TempDB" to "tempdb".
That's it for now. I'll now start properly reading the article.
Heh... thanks Hugo. Despite our differences on several of those subjects, you've always been a gentleman in your criticism and observations. Never any harm done there and I very much appreciate it.
On the subject of using undocumented features, I agree... you can't call Microsoft on them. If they ever change the fundamental code behind the UPDATE statement so the "Quirky Update" doesn't work anymore, then we'll all have to resort to a Cursor, While Loop, or a CLR. Hopefully MS will have a properly working SUM() windowing function by then, as well. Heh... the good part is... they're done modifying 2k and 2k5 so I don't believe we have anything to worry about there. 🙂
On your item 2, agreed... I don't use any case sensitivity except by column so I tend to overlook the folks that have an entire server set to be case sensitive. Hmmmm... perhaps that would make a decent "Friday Poll"... "Question 1. Do you have any case sensitive servers? Question 2. Either way, please discuss why or why not." I think it would be a great discussion.
Again, even though we tend to disagree on a couple of subjects, I always appreciate the very thoughtful manner in which you discuss those things. Thanks, Hugo.
--Jeff Moden
Change is inevitable... Change for the better is not.