Celebrating the first day of Summer with the first technical post on the blog since mid-Winter (apologies for my absence)..

Jens has a great post regarding the internal behavior of various forms of sp_-named programmability objects.  Some SQLRAP-related research led me to take a different tack, and I wanted to share the results of that research here as well.

So here are five things about sp_ to be aware of.  I’ve verified all of these behaviors on SQL Server 2000 and above (through SQL Server 2008 R2).

1. If we load onto a user database a programmability object named identically to one of the MSFT-supplied sp_ procedures (“sp_who”, “sp_lock”, “sp_monitor”, etc.), that code will never be executed.  The code in master will always run, even if we issue a three-part call to your user-created object.
   
2. If we load such a programmability object into model, we’re essentially replicating issue 1. above in every subsequently created database.
   
3. If we load our own sp_ programmability object (with a name which doesn’t match that of a MSFT-supplied object) in a user database and another identically named object in master, things get even more complex.
   
   In the user database where the programmability objects resides, a one-part call will execute the copy in the local database -- the opposite of the behavior for MSFT-supplied sp_ objects discussed in item 1. above.
   
   In other user databases, a one-part call will execute the copy in master – identical to the behavior for MSFT-supplied sp_ objects described in item 1.
   
   Confused yet?
   
   What if the code in these two objects is different?  Then a one-part call will behave differently in different database contexts.
   
   What if the object name exists in multiple user databases, each with different code under the name?  The problem multiplies.
   
   
4. By this same logic, I trust it’s clear that we wouldn’t want to load multiple copies of our hypothetical sp_mycode into any user databases without loading a copy into master.  Other than an affection for the convention, there’s no real reason to do this, and confusion can result if we subsequently load a new or copied sp_mysproc into master.
   
   
5. The only possible permutation which remains, then, is a single instantiation of sp_mysproc in master.  Lots of people do this and survive just fine, but given the foregoing, this approach strikes me as an unnecessary potential source of instability.
   
   If the objects are instead housed in a dedicated database, there are several ways to access them via a one-part call.  Use synonyms where available (SQL Server 2005+); in SQL Server 2000, load like-named objects on each user database which simply execute the three-part call to the object in the dedicated database.  We could even load such objects in model to ensure that our utility objects are accessible from newly created databases.
   

So -- surprisingly enough on a Microsoft-hosted SQL Server blog -- my recommendation is that you leave the sp_ object names to Microsoft, and use one of the workarounds discussed above to implement a naming convention which behaves consistently across your instance.

The headaches you save will likely be your own.

Does your shop have any rules regarding use of sp_?  Do you have any stories to tell -- good or bad -- about using it with user-created objects?

Contact me if you have any questions or comments, or if you’d like simple repro scripts which demonstrate these concepts.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites