Microsoft: "Don´t use the prefix sp_"

quote:
I use my initials on stored procedures and require that anyone else who creates stored procedures use their initials. It's an easy way to keep track of who made what.
-Bill

Antares686 SSC Guru Points: 125444 More actions · Answer 4

quote:
My initials are sp.
Sean P
quote:
I use my initials on stored procedures and require that anyone else who creates stored procedures use their initials. It's an easy way to keep track of who made what.
-Bill

As long as you use spXXXX and not sp_XXXX there will be no issue for you, just when first three are sp_. But I see what you mean.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 5

There is also always the option of going to three letters with something like a z or q used if someone doesn't have a middle name.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

spireite Mr or Mrs. 500 Points: 569 More actions · Answer 6

Personally, we've standardised on the Company Initials then SP.

Hence ACLSP - we can instantly determine our SPs from other vendors SPs in one database if third party tools are used.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

The only problem when I thinking about it, with using initials is it makes it harder to code as you have to go look up the SP name. What do you do if that person leaves and then someone else alters it, of course they had best not change the name of the SP. I prefer a standard that makes simple sense without the need to go back to look it up.

Ex.

I create a table tbl_PagingMsgs

Then when I create my SP i use

ip_UPagingMsgs -- For Updates

ip_IPagingMsgs -- For Inserts

ip_DPagingMsgs -- For Deletes

ip_SPagingMsgs -- For Selects

And of course I document in the code itself the information on when created, modified by whom and what each does even thou most names are desciptive enough on that part (There was a thread on this awhile back that if anyone is interested in they should search for it or ask and maybe I can find).

This way names are generic, understandable and have no conventions that make looking them up always neccessary. Some folks may be lucky enough to not to have to make code changes later to client or server side stuff but my environment changes too often for that to be a luxury.

(Just my thoughts on SP with initials of developer)

FHellebro SSC Veteran Points: 262 More actions · Answer 8

Thanks all

I think I've got enough info to argue for a new prefix. Just a small jobb, a week or two 🙁

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 9

Last note:

We use a prefix usp for user stored proc and SUFFIX identifiers. The IDs are only for dev and do not make it to the production system. Internal comments show who made what changes and when. Since SPs are compiled, there is no penalty for more ignored bytes.

timgarner Newbie Points: 9 More actions · Answer 10

Does anyone have an idea how much of a performance hit this causes? Also, if the owner of the sp_ is included in the exec will I still get a Cache Miss? Thanks!

Tim

Antares686 SSC Guru Points: 125444 More actions · Answer 11

quote:
Does anyone have an idea how much of a performance hit this causes? Also, if the owner of the sp_ is included in the exec will I still get a Cache Miss? Thanks!
Tim

Not sure how bad the hit is, but if run often of course the hit adds up. And specifying the owner does not help.

goran-615577 Grasshopper Points: 18 More actions · Answer 12

Interesting debate. I have heard this discussion many times over the years. Let me just make this statement: The amount of time it costs to introduce/change a namestandard (sp_ to usp_ or whatever) is big. However I can not recall a single case that I have heard of where this penalty hit actually was a problem. I am currently working in a system with sp_ as naming convention, no problems. (One of the larger banks in the country)

All to often programmers tend to spend many hours on stuff that never pays off: "Lets just do this thing and then in the future if this other thing happens we can save a lot of time". Well in the future the other thing never happens! Cleverly designed multi-tier solutions get scratched in favour of SOA for example...

Here is my 0.02$: Call the darn procedure whatever you like. I you have 2 applications that reside in the same database (Customer frontend and Manager backend) call the procedures fe_ and be_ for easy grouping.

Best wishes!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

The other problem with the sp_ (don't know if it was mentioned, I didn't read the entire thread) is the possibility of overlap with a system proc (current or future)

If your proc is prefixed sp_ and has the same name as a system proc that's in the master/resource db, then your proc won't get executed. The call will resolve to the system proc and not to yours.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 14

I don't think it's a big hit, but I agree with Gail's issue with naming conflicts.

I'd avoid it, script out the procs and make the changes, You could even make the changes inside procs with an easy search replace. I have worked with vendors that did things like ip_myproc and it made it easy to see their procs v mine.

goran-615577 Grasshopper Points: 18 More actions · Answer 15

WTF, this is like kindergarten!

Gail, when you have found one (1!) application in the whole universe that uses the same name on a system stored procedure as on a user stored procedure, then you can call me. Until then don't waste my time with extremely special scenarios that u need to be on crack to fully appreciate.

Nah, this kind of academic discussion don't do it for me, I'm off!

/Goran

p.s Steve thanks for the tip on search and replace, most helpful! d.s