By default, the stored procedures in SQL Server have a pretty easy naming convention:
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).
- Regular stored procedures are prefixed with "sp_".
- Extended stored procedures are prefixed with "xp_".
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).