Why do we have undocumented and unsupported functions in SQL Server?

Question

After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long time and are quite useful. Same with this sp_msforeachtable and sp_msforeachdb article from 2004.

I could benefit from one of them to answer a question on the forum (During a log backup is the data backed up to the start or end of the operation?), but I'd like to know why they're shipped with SQL Server in a undocumented and unsupported way until now.

I understand they're said not to be reliable, but if that was the only reason, in 16 years they could have been corrected. So there must be another explanation.

Answer 1

As someone working for a large software vendor, I can say that undocumented and unsupported¹ features typically fall into one of these three categories:

1. Tools for the exclusive use by the vendor support engineers. They are rarely needed, typically require intimate knowledge of internals, hard to set up and use, and often dangerous enough to be protected by one-time passwords or keys that should be explicitly provided by the vendor. They are never intended to become product features. They are there to aid support when troubleshooting hard to debug problems.

2. Fixes and workarounds for esoteric bugs. They are implemented as hot fixes for critical situations. Such bugs are rare, and their root causes are eventually resolved, so these "features" lose their usefulness at that point, and it's not in the vendor's interest to provide continuous support for them.

3. Beta and experimental features. They are typically first made available and sparsely documented for a small cohort of beta testers and major customers who requested them. Some of these will eventually become fully supported, some others will be left there to rot when stakeholders lose interest or when they conflict with other features that are deemed more important or useful.

As mentioned in the comments to your question, each fully supported and documented feature carries a cost to the vendor, and that cost must be justified by the expected benefit. If there is little benefit to the vendor from maintaining a certain feature, there's no reason to support it.

---

1 - They are not really totally unsupported; they are supported on a "best effort" basis, meaning "We might have a technote somewhere that briefly describes the feature, but we won't spend much time helping you make it work, and if it doesn't work or if it wipes out your data, well, tough".

Answer 2

I remember having to use one a long time ago. Ever try to change the SQL Compatability level of a database from inside a transaction? Turns out you can, if you copy the contents of the sp_ command that did it. (Disclaimer: this code only had to work on exactly SQL 2005 and its last service pack had already been released.)

In older times, the features you were only supposed to be able to access from the UI were implemented using various kinds of nonstandard syntax calling sp_ commands or xp_ commands. xp_ commands were hardwired, but there really weren't all that many of them. sp_ commands just contained TSQL that did stuff. They way they were actually implemented was undocumented, but you could use sp_helptext to read the code. Doing so was usually a bad idea.

Then there's one more undocumented feature people still use. The old password hash functions from SQL 7 are still there and callable. They're long undocumented and nobody should be using them anymore, but if you have an ancient application that still has to check passwords last changed in the prior century, it's the only way.

And there's ALTER DATABASE SET EMERGENCY that used to be something even more wild in even older versions. MS was forced to make a proper documented command out of it because of people finding the undocumented command and missing the rest of the procedure to use it. In this case, laziness caught up with them.