Menu
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?
925
There is no valid reason to name a stored procedure with the “sp_” prefix, unless you are intentionally creating a procedure in the master database. Sending SQL Server to the wrong database to find your stored procedure is not very optimal. It is confusing, inefficient, and can cause performance problems.
Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.
I believe that usp is short for "user procedure". That distinguishes it from the system procedures prefixed "sp_". That is an important distinction, as you can read in the answers.
To rename a stored procedure Expand Databases, expand the database in which the procedure belongs, and then expand Programmability. Determine the dependencies of the stored procedure. Expand Stored Procedures, right-click the procedure to rename, and then click Rename. Modify the procedure name.
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.
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
