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

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.

Sponsored link:

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).

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