SQL SERVER – BEWARE OF USING PREFIX “SP_” IN USER DEFINED STORED PROCEDURE

During the Analysis of a database, I found some procedure names starts with “sp_” .This is a small mistake, but mistake is mistake and we have to overcome it.This will reduce the performance of your  SQL Server . Most of the people in IT Industry will simply ignore this statement because what makes a performance issue by naming a procedure using “SP_” ?. But the truth is it will reduce the performance a little bit. Let’s look into it,

  • What happens if procedure name starts with “SP_” in SQL Server?

While executing the Stored Procedure, the SQL Server Engine will First search for the system stored Procedure starting with “SP_” in the master databases. After completing this search only, it will consider the User defined Stored Procedure. By changing the naming convention to something like “USP_” will  improve the Performance of the database. Also, if there happens to be a system stored procedure with an identical name, your procedure won’t get processed at all.

  • Conclusion:

SQL Server Performance relies on various factors. Here we see a simple naming convention can improve the performance. Design the database using Good Experts. Otherwise you have to pay for Performance.