Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Undocumented Routines: Forbidden fruit.

By Phil Factor,

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.

Total article views: 374 | Views in the last 30 days: 1
Related Articles

Undocumented Routines: Forbidden fruit.

Comments posted to this topic are about the item [B]Undocumented Routines: Forbidden fruit.[/B] Once...


Undocumented Extended and Stored Procedures

Some useful undocumented extended and stored procedures in SQL Server 2005


Unable to use SQL Server because ASP.NET version

Unable to use SQL Server because ASP.NET version


The DBA Routine

What's the routine of a normal DBA job? Too many people work alone and don't get feedback or comment...


SQL Server 2000 Undocumented System Tables

Alexander Chigriks decodes some SQL Server 2000 Undocumented System Tables.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones