The use of undocumented database routines such as functions, procedures, DBCC commands or trace flags in SQL Server always makes me feel slightly uneasy. I don’t think there is any hard and fast rule as to when and where they should or shouldn’t be used, but, in every case, these things have been left undocumented because Microsoft doesn’t want you to use them. In the majority of cases, they have good reasons for discouraging their use.
It is difficult to avoid a twinge of sympathy for Microsoft when they release a new version of SQL Server and they get castigated for it being ‘broken’, because a database that worked on a previous version no longer does so. It is not always their fault. I continually come across database code that could never work on a later version of SQL Server because of its reliance on undocumented routines.
Of course, if you are inexperienced, then it is best to keep to a simple rule: if it isn’t in Books-On-Line (BOL) then don’t use it. It is so often the case that there is a documented way of doing what you are trying to achieve. If you’re cornered, then use it, but put in a version check that raises an error if the version isn’t what you’ve tested the routine on and explains what you’ve done.
Like so much of life, hard and fast rules never bear close examination. When I’m under pressure, and trying to get a job done, I’ll use all sort of shortcuts, such as sp_msForEachTable or xp_regread in order to achieve what I want quickly. As long as this is ephemeral code, I reckon it is OK: As soon as it becomes enshrined in a procedure, then it isn’t. We also have to consider the minority of undocumented routines where Microsoft discourages their use for bad reasons. I include DBCC Log, and its offspring fn_dblog, where there seems to be no documented way of doing perfectly legitimate actions to maintain or audit your own data.
