Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Why you should not prefix your procedures with "sp_".

Discussion in 'Performance Tuning and Optimization' started by Peter Schmitz, Oct 17, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    By default, the stored procedures in SQL Server have a pretty easy naming convention:

    • Regular stored procedures are prefixed with "sp_".
    • Extended stored procedures are prefixed with "xp_".
    Most people developing against SQL Server will find themselves inclined to prefix their own stored procedures with "sp_" as well, to easily identify objects as procedures.

    However, doing so will have a slight performance impact. As it so happens to be that by default, the SQL Server engine will first check the master database whenever a procedure prefixed with "sp_" is called, as it assumes that procedures prefixed with "sp_" are system stored procedures.When it does not encounter the procedure in question in the master database, it will check the context of the database the procedure is called from. Due to the behaviour of first attempting the master database, performance is negatively affected.

    Other than that, suppose you name your procedure "sp_check_waittime", and in a future release of SQL Server, Microsoft creates a procedure with the exact same name, it might lead to confusion.

    Therefore, it is recommended to prefix your own stored procedures with a different prefix. The one prefix I have come across most, and which I have extensively used myself is "usp_" (u for user-created).
  2. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    Quite a while ago, I received a private message from a reader who wondered whether the advice about the sp_ prefix still was valid. I had to admit that I was not entirely sure, and that perhaps old habits just died hard.

    But yesterday on Twitter, Kevin Kline happened to tweet a link that shows the information in this thread still is valid enough, albeit perhaps for slightly changed reasons. Read the artticle on SQLPerformance.com:

    http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Share This Page

Sponsored link: