There is a common practice with some development teams that they prefix any user defined stored procs with “sp_”. It’s a bad practice in a way that it was a recipe for disaster when you strike the bulb.

What I mean is that, the system stored procs in SQL Server are named starting with “sp_”. Now, when you try to name your user-defined stored procs starting with “sp_” what happens is that in addition to bringing down the performance it might give you a “gotcha” moment.

Here is how SQL Server looks for a stored proc starting with “sp_” in this hierarchy of databases,

1. Master DB
2. The stored procedure based on the fully qualified name provided
3. The stored procedure using dbo as the owner, if one is not specified

So, when you’ve named your user-defined stored proc starting with “sp_” in a database other than Master database, the Master database is always checked first when you explicitly don’t specify the database name and so when you’ve accidentally named your user-defined stored proc similar in name as the system stored proc then the user-defined stored proc will never get executed.

For example, let’s say you’ve accidentally named your user-defined stored proc as “sp_help” with the definition,

create proc sp_help
as
select * from dbo.login

Now, when you try to execute the above created proc using the

Exec sp_help

you’d be surprised to see that the sp_help under Master database has got executed than from your database.

To deal with this it is a best practice to use “usp_” rather than “sp_”. At least that’s what I prefer to use.