Avoid Naming User Stored Procedures SP% or SP_%

sql-server stored-procedures query-optimization

Does a user stored procedure with the prefix of SP (not SP_) have poorer performance by looking in the Master DB (similar to user SP named SP_) or does it look in the DB where the stored procedure exists because it lack an underscore?


Solution 1:

The reserved prefix that exhibits this behaviour is sp_. Not sp.

A stored procedure called spAddUser will be resolved in the normal way without looking for a matching object in master.

The relevant quote in books online is

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

But I would avoid these prefixes anyway. If all the stored procedures are prefixed sp it quickly gets annoying IMO.

Related

SimpleDateFormat giving wrong date instead of error
How can I initialize an array using a function?
How to merge the two cells of a table in a Google text Document without this weird result?
Comparing character arrays and string literals in C++
lapply-ing with the "$" function
How to force older debian to forget about DST Root CA X3 Expiration and use ISRG Root X1 - SSL certificate problem: certificate has expired
Which C datatype can represent a 40-bit binary number?
Sleep for exact time in python
How to sort array of objects based on a boolean property?
"In" operator for numpy arrays?
How to get the actual (localized) folder names? [duplicate]
Providing an argument that has not the TARGET attribute to a procedure with a dummy argument that has the TARGET attribute