Avoid Naming User Stored Procedures SP% or SP_%

问题:

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?

回答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.