Why it is not recommended to give store procedure name with sp_ prefix

Question

Accepted Answer

Using sp_ prefix for stored procedures can cause performance issues and conflicts with system procedures.

sp_ prefix is reserved for system stored procedures in SQL Server, so using it for user-defined procedures can lead to conflicts.
Using sp_ prefix can cause unnecessary overhead as SQL Server first searches in the master database for system procedures before looking in the user database.
Avoiding sp_ prefix can improve performance and prevent confusion with system procedures.

Accepted Answer

SQL has pre defined stored procedure with sp_ prefix in master database. When store procedure with sp_ is executed, SQL searches in master database and then searches in specific database. To avoid multiple database calling it is recommended not to give sp name with sp_

Why it is not recommended to give store procedure name with sp_ prefix

Top Lionbridge Technologies Senior Database Developer interview questions & answers

Popular interview questions of Senior Database Developer