Stored Procedure Naming Conventions

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:

1. The master database is always checked first, even if the proc is qualified with the db name.
2. 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

• http://msdn.microsoft.com/en-us/library/aa214379(v=sql.80).aspx