Friday, October 24, 2008

Stored Procedure naming conventions

I got a question about stored procedure naming conventions today. I spent a bit of time thinking about it and here is what I advised the client:

  • The most important thing is to have a standard that is accepted by the whole team and is followed (possibly enforced). The actual standard that is adapted is not very important. You should remember that there are two main goals that the standard (naming convention) is intended to achieve:
    • Avoid potentially costly mistakes (for example dropping a table while you think you are dropping a view)
    • Improve the productivity of the developers by making the code much easier to read (understand) and reducing the time it takes to locate an object.
  • Given those two simple objectives it follows that the name of the object should tell us:
    • What type of object it is;
    • What is the base object on which this depends (if applicable)
    • What does it do (Insert, Delete, Update, Create etc.)
    • Other group identifiers if necessary

As far as specifics the only warning I am aware of (this was valid in SQL Server 2000 and I am not sure it applies to 2005) is that you should not prefix the stored procedures with sp_ - there is an article by Brian Moran on SQL Server Magazine ( that explains why.

No comments: