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?
Asked
Active
Viewed 3,753 times
9
-
Suggestion. I use "dbo.uspEmployeeGetSingle". "usp" prefix. then entity, the action. – granadaCoder Dec 11 '13 at 22:16
1 Answers
12
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.
Community
- 1
- 1
Martin Smith
- 438,706
- 87
- 741
- 845
-
When all SPs are prefixed by sp, what's the point? this applies to everything else, hungarian notation rarely makes sense – BlackTigerX Dec 11 '13 at 22:37
-
@BlackTigerX - I agree with you. It is normally pretty obvious from context that an object is a stored procedure (whilst you can also `EXEC` scalar UDFs hardly anyone does). Less annoying than the `tbl` prefix though. After refactoring databases using that you can easily end up with backward compatibility views replacing tables which still have the `tbl` prefix that is supposed to denote a table!) – Martin Smith Dec 11 '13 at 22:42
-
@MartinSmith Does SP_ (upper-case) have the same effect as the lower-case sp_ prefix? – user797717 Sep 05 '15 at 14:10
-
1@user797717 If the instance is case insensitive it does (as it still needs to resolve references with SP_ to the same system objects) but on case sensitive collations it doesn't. – Martin Smith Sep 05 '15 at 14:14