In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic.
In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that.
The nay-sayers argument are valid
- Ordered updates are not supported by Microsoft
- It is an undocumented and unsupported feature which may break in next service pack or version of SQL Server
Most of the nay-sayers reference to the traditional "Quirky Update", where fellow MVP Jeff Moden has written an excellent article about here. There are a number of situations where the "Quirky Update" will fail and these are the arguments used by the nay-sayers.
Looking at the blog post made by Mladen, all those pitfalls are out of the equation. There is no parallellism (too small dataset), no partitioning (not possible on temporary tables) and definitely no clustered index (the temporary table is a heap).
Looking at the execution plan reveals the table scan is ordered and the Sort operation is ordered. I have yet not been able to break the Ordered CTE Update (except using a clustered index).
Some argument were that ordered Views are not supported with SQL Server 2005 and later. And if it did happen to work in SQL Server 2000, it was just by coincidence. Keep in mind that a CTE is not a view, a CTE is a temporary named result set. The result set is derived from a simple query and defined within the execution scope of a single statement. So, the CTE is not available for other sessions as the VIEW is.
Now, my point is that the nay-sayers are correct. Microsoft has not supported ordered updates and probably never will due to Relational Theory. But, yet the Ordered CTE Update still works every time!
Yes, it is unsupported and may broke in next service pack or version of SQL Server.
But... Even fully documented and supported functions and procedures in SQL Server will change!
The last time it happened was when REPLACE function changed behaviour. That change alone broke our CRM application completely when upgrading to SQL Server 2008. Bummer.
Who remembers the procedure sp_makewebtask? Please raise your hands. That procedure was introduced with SQL Server 2000 and lot of people started to code using that procedure. In SQL Server 2005, the procedure was marked for deprecation and changed permissions to sysadmin. Finally it was removed in SQL Server 2008. So much for the documented and officially supported procedure.
What can we learn from this?
Well, the first thing to learn is that everything changes. For better or for worse.
Should you use an undocumented or unsupported technique?
Well, it depends. Using system tables were never supported nor officially documented but widely used anyway. For example setting a database in suspect mode, which now you use ALTER DATABASE for. Editing the system tables are now locked with SQL Server 2008.
The REPLACE function changed, so how do we know if there are more built-in functions that will change in the future. Does it mean we shouldn't use them?
And for GEOGRAPHY datatype, there was a major change when latitude and longitude changed positions! That caused a lot of confusion and maintenance of location tables...
How many of you are using DBCC LOGINFO to keep track of your VLF's? It's unsupported and undocumented! But VERY handy indeed...
There are more breaking changes of which I will not write. You get my point now, I think.
So what do to?
If you ask me, use all the tools available for you to do your job. But document the unsupported code you use and have alternatives ready if they break in the future.
I read, not long ago, about an analogy for portability. The writer stated that database code using SQL language always should be portable to any platform, so you should only use standard ANSI supported code.
The immediate answer to that statement was, of course, "Why buy a scientific calculator and find out you are only allowed to use plus, minus, division and multiplication?".
At the end you have to decide for yourself. Both approaches has it pros and cons.
Have fun
//Peter