SQL Server undocumented commands

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    I was going through some blog post today and noticed use of some ‘undocumented commands’ in the post.

    Per my understanding, if Microsoft didn’t document the commands they might have a good reason for that. Also, Microsoft doesn’t support such practices.

    I would like to know community’s opinion on use of such commands.

  • Lowell

    SSC Guru

    Points: 323444

    no...I say use the undocumented stuff whenever it's needed.

    technically sp_MSForEachdb and sp_MSForEachTable are undocumented and unsupported, but widely used.

    many times, MS is calling something an "undocumented feature", but added needed functionality, but without the official need to support it.

    officially supporting it costs developer and support time, so it's often much easier to add a functionality, declare it unsupported, but effectively resolve an issue anyway.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Perry Whittle

    SSC Guru

    Points: 233824

    take xp_delete_file. This proc is undocumented but yet is called by maintenance plans to prune aging text\backup files.

    It seems to me the undocumented procedures are generally the ones that SQL Server uses internally and they may change at any point (CU or SP).

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    Perry Whittle (10/10/2011)


    they may change at any point

    Isn’t it a reason to worry?

  • Perry Whittle

    SSC Guru

    Points: 233824

    Dev @ +91 973 913 6683 (10/10/2011)


    Perry Whittle (10/10/2011)


    they may change at any point

    Isn’t it a reason to worry?

    no, as Microsoft advise hwne using any of these procedures be prepared for them to change unexpectedly and re code your sequences that use them. And of course, you don't go applying CU's or SP's without first testing and checking what changes they bring 😉

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    And of course, you don't go applying CU's or SP's without first testing and checking what changes they bring

    For fresh developments it’s fine. What about the existing implementations?

  • spaghettidba

    SSC Guru

    Points: 105661

    It depends! (You should have seen that coming :-P)

    For production code I tend to avoid the use of undocumented commands/features. For my own scripting I make a fair large use of undocumented commands, such as sp_MsForEachDB.

    However, if the undocumented thing solves an issue, I have no problem with using it in production. I just make sure it still works when I apply SPs and CUs.

    In my opinion, commands such as sp_MsForEachDB are so widely used that I highly doubt MS will ever remove them from future versions.

  • Perry Whittle

    SSC Guru

    Points: 233824

    Dev @ +91 973 913 6683 (10/10/2011)


    What about the existing implementations?

    That's exactly what I am referring too!!

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • steveb.

    SSC-Forever

    Points: 46733

    is sp_who2 still undocumented?

    as i use that all the time.

    Also DBCC Page now and again..

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    That’s great input guys.

    What I am really looking for:

    ‘Has anyone burnt his hands in such practice? Any major issues / concerns.’

  • Perry Whittle

    SSC Guru

    Points: 233824

    Dev @ +91 973 913 6683 (10/10/2011)


    Has anyone burnt his hands in such practice?

    No, because we test new CUs or SPs and check what effect they have on the systems. Always check the release notes to see what has been included\removed

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    When I read the comments it looks like majority is in favor of it. However poll results don’t support the same.

  • steveb.

    SSC-Forever

    Points: 46733

    I dont think a poll of Four people is really going to tell you anything, give it some time maybe..

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    give it some time maybe..

    Yup… this is what I am going to do now… Leaving for the day 😀

  • SQLRNNR

    SSC Guru

    Points: 281243

    Gianluca Sartori (10/10/2011)


    It depends! (You should have seen that coming :-P)

    Though undocumented, as has been said, there is evidence of Microsoft calling their own "undocumented" code. I think it really comes down to "It Depends" as Gianluca said.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply