Why Shouldn't We Begin a custom/user-defined Stored Procedure Name with sp_?
Ever wondered why we don't (or shouldn't, if you do) prefix procedures with an sp_?
As a primarily .NET developer, while doing any database work, earlier I hadn't paid much attention to why all stored procedures in the company where I worked in, began with a prefix of "p". So we would name them along the lines of "p_GetXyzDataByParam1Param2". Obviously, I could see that "p" is the convention in the company, but would it cause any impact if we name them the way system procedures are named (i.e., sp_? Turned out, that yes, it could.
We do know that the system stored procedures are named with _sp, and are stored in the master database. But if any stored proc does start with an sp_, SQL Server tries to find any such proc in the following order:
- in the master database
- based on qualifiers like database name or owner
- using owner dbo if it's no owner is specified
In other words:
- The master database is always checked first, even if the proc is qualified with the db name.
- If any user defined stored proc has the same name as a system procedure, the system proc will be executed (and the user proc will never be executed).
Conclusion
It is recommended that stored procedures shouldn't be prefixed with sp_ for two simple reasons:
If a user-defined stored procedure has the same name as a system stored proc, the user-defined procedure will never be executed.
The master db will always be checked first because of the prefix of sp_.
- Possible conflict of user created stored procedures with system stored procedures
- For performance
References
