Unsupported and/or undocumented features

Wed Feb 10, 2010 by Peter Larsson in sql-server-2008, sql-server-2005, miscellaneous

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

Legacy Comments


Harsh Athalye 2010-02-10	re: Unsupported and/or undocumented features I agree with you, Peter. There were lot of undocumented procedures, most widely known being sp_who2. It was never documented but everybody using it even now. Who can forget MSforeachdb and MSforeachtable. There were and there will be undocumented routines and as far as you documented it and provided an alternate fix, there is no issue in using it. There is always trade off between portability and efficiency. The choice is of implementor.

Isaac Kunen 2010-02-10	re: Unsupported and/or undocumented features Hi Peter, Yeah, we have undocumented features. Some of these are like some of the ordering semantics that we currently have. Barring an explicit ORDER BY, we don't guarantee the order of a query, but we may pretty reliably generate things in a particular order. Then we change something and break some poor guy's query. It sucks, but what are we to do? But by the same token, my sense is that in the past we've used the undocumented excuse to either ship features that were not fully baked or skip our normal deprecation procedure. In the SQL Engine at least, we're trying to change this attitude. We may still ship undocumented features, but we should not have the position that because they're undocumented they don't count as part of the product, nor should we assume that users haven't taken dependencies on them. Don't expect this to resolve overnight, but do expect us to get better on this front. I'd be remiss if as the former spatial PM I didn't address the GEOGRAPHY example, which doesn't seem apropos: it wasn't about undocumented features at all. In that case, we shipped a CTP and changed the coordinate order for a subsequent CTP based on extensive user feedback---all very public and fully documented. This seems like the exact kind of responsiveness I'd hope for as a user of the product. No? Cheers, -Isaac Kunen, Microsoft SQL Server

Peso 2010-02-10	re: Unsupported and/or undocumented features Hi Isaac! Thank you for your post. For the Geography, I am all for the changing of positions for latitude and longitude. Now they have the order (no pun intended) they "should have" from the beginning. My frustration was that I had done a lot of presentations and labs using the datatype and suddenly I had to change them all. No big deal, it's a CTP, but nevertheless. The change of REPLACE was a real showstopper. We initially missed the information in Books Online about the change, but when we had Books Online, it was too late because SQL Server 2008 was already installed and the databases was upgraded. It would be really nice to have the changing behaviour published in advance. At least now I know to download next Books Online beforehand to install and read, before even considering installing SQL 2011. My point above with the REPLACE and GEOGRAPHY, is that even fully documented and supported features do happen to change in the future. The question is which and to what extent. The thing I now most is afraid will happen, is a change in the XML implementation. Perhaps it will never change, or it will change due to some internal performance implementation. Don't get me wrong here, I am all for changes to make SQL Server a better and/or faster product even if it means breaking past behaviour. And that's the thought of the initial blog post; things will change. Connect is an excellent place to gather user input, I have used it some times in the past and will continue to do. Keep up your good work, Peter

Mike C 2010-02-10	re: Unsupported and/or undocumented features Hi Peter, Are these really examples of unsupported and/or undocumented features? - The REPLACE function is fully documented and fully supported. The fact that the documented behavior changed in a new version of SQL Server doesn't make it an unsupported and/or undocumented feature. - sp_makewebtask was fully documented and fully supported on SQL 2000 and 2005. It was deprecated in 2005 as you say, but it was documented and supported on that platform. - DBCC LOGINFO was unsupported and undocumented in the past, but since 2008 the folks at CSS have been telling people to use it(http://blogs.msdn.com/psssql/archive/2008/09/12/sql-server-2000-2005-2008-recovery-rollback-taking-longer-than-expected.aspx , http://blogs.msdn.com/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx). That indicates some level of documentation and support, although I would call it one of the "poorly documented features" which we run into from time to time. I guess the question is are you talking about unsupported and/or undocumented features, like reliance on the query optimizer to operate on rows in a specified order during set-based operations, or are you talking about supported and documented features on specific platforms? As you explained, a documented/supported feature on one version of SQL Server may not be documented and supported on a different version. Thanks

Peso 2010-02-10	re: Unsupported and/or undocumented features Hi Mike. I am trying to see both sides in this matter. The nay-sayers says the Ordered CTE Update shouldn't be used at all because it is not supported, and they are right. Even a CTE is not guaranteed by Microsoft to return records in an ordered fashion, even with ORDER BY clause. This is the unsupported feature I made use of in the competition because, even if not supported, the CTE does update the records in the my wanted fashion. The only thing that I have found to break it, is a clustered index. Which don't exist in my solution. However, the nay-syers are right. The solution might not work after next service pack or in next version. My counter-argument to the nay-sayers are the REPLACE and sp_makewebtask features. Even if they are supported and fully documented they either changed behaviour or was completely removed. Who would thought REPLACE would break an application, when the application was designed and coded years ago? The REPLACE was and is supported and fully documented in Books Online. Everyone can see that. If my solution for the competition had incorporated the REPLACE function and worked in SQL Server 2005 but not SQL Server 2008, what to say then? Is it my fault? Should I have anticipated a breaking change in a built-in function? Does it mean my solution shouldn't be used because some other function might change in the future? What if OUTPUT clause has a breaking change in the future? What then to say to all people who today recode their solutions away from @@IDENTITY and SCOPE_IDENTITY()? These functions everyone thought were safe at the moment they were introduced to SQL Server. Today there not that safe with the raising complexity of concurrency, and OUTPUT will help them tremendously. Until the day when OUTPUT eventually change. Or not. So what is the right thing to do? Use undocumented features which may break (or not), or use "riskfree" and built-in features no-one ever thought will change, but did? Does it mean we can never use REPLACE again, if it changes a second time? It is supported and may change with documentation, but it still requires a lot of work to recode an application to follow the new guidelines for the REPLACE function. Same thing goes for the undocumented features. They are not guaranteed to work, and may change with next service pack or version. So what is the difference, in an application maintenance viewpoint? If there is a future change, there will always be hard work and recoding involved, both for supported and unsupported features. //Peter

Peso 2010-02-10	Comment for OUTPUT statement above What I mean with the OUTPUT change, is that today the OUTPUToperator supports not only the INSERTED and DELETED virtual tables, but also the tables used in MERGE command (as UPDATE, DELETE also does). MERGE... AS tgt USING... AS src ON .... WHEN MATCHED... WHEN NOT MATCHED... OUTPUT inserted.Col, tgt.PkCol is supported today. Will it change in the future? I hope not, but I can't neither anticipate nor guarantee it.

Mike C 2010-02-11	re: Unsupported and/or undocumented features I understand. They even changed the syntax for MERGE between the CTP and the release version, but that's to be expected... I don't know if it will change in the future, but my point is more centered on the title of the article -- "Unsupported and/or undocumented features", but what you really seem to be addressing is documented breaking changes to documented and supported features. Granted, a breaking change causes a lot of issues, but MS is much better at documenting deprecated features prior to their removal and documenting breaking changes after the fact than a lot of other companies/software products. Java comes immediately to mind here. IME unsupported and undocumented features tend to be relatively easy to avoid, it's the "poorly documented features" that really cause me a lot of headaches.

Plamen Ratchev 2010-02-11	re: Unsupported and/or undocumented features Hi Peter, I think you make the wrong argument here. Unsupported /undocumented features are one thing, bad practices are another. To me claiming and supporting “ordered CTEs” concept is pure bad practice. First, it violates the very basic relational principle the sets are unordered (yes, I do know specific implementations in past SQL Server versions resulted in abusing views, but that has been clarified). Next, as Connor Cunningham stated "There are no guarantees on rows coming out of a CTE in any order from the Query Optimizer in any case (not just updates)". That in essence clears any confusion about CTE implementation in SQL Server. And it is not a matter of "can you break my code to prove me wrong". It is a matter of what is the proper way to code things and teach people good practices. Because most will simply copy/past piece of code and next thing you know it breaks, then who is to blame? Plamen

http://www.lejeupoker.com/ 2010-05-19	re: Unsupported and/or undocumented features Undocumented features are frequently found in computer software releases. Sometimes the documentation is omitted through simple oversight, but undocumented features are often elements of the software not intended for use by end users, but left available for use by the vendor for software support and development.

NLP 2010-11-05	re: Unsupported and/or undocumented features Undocumented features are in every software and everywhere , in most of the cased they must learn by themselves about how they can be used and implemented or used for other features. Well maybe in the future all developers will be more carefull with their work.

Jeff Moden 2011-07-05	re: Unsupported and/or undocumented features Peter, Just two simple words... well done! --Jeff Moden