Stored procedure naming prefix

Question

Post reply

Stored procedure naming prefix

l0n3i200n

Ten Centuries

Points: 1091
More actions
July 2, 2008 at 3:32 am

#188897

Hi
I've noticed at our company stored procedures gets prefixed with "sp" and not "sp_". My question is does "spStoredProc" have the same penalties as "sp_StoredProc"
I know about "sp_" but what about "sp" ?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.

'SP' should be fiine.

adnan.korkmaz SSC-Addicted Points: 421 More actions · Answer 2

steveb (7/2/2008)
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.
'SP' should be fiine.

So, isn't some similar scanning occur when all the stored procedures start with the same prefix? ('SP' in this case). Not maybe for the system databases, but within the procedures in that specific context?

Although I have not tested it properly, it seems that one should name his/her stored procedures as unique as possible for the maximum performance, starting from the first character.

Were any tests done on that?

steveb. SSC-Forever Points: 46733 More actions · Answer 3

adnan.korkmaz (7/3/2008)
steveb (7/2/2008)
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.
'SP' should be fiine.
So, isn't some similar scanning occur when all the stored procedures start with the same prefix? ('SP' in this case). Not maybe for the system databases, but within the procedures in that specific context?

BOL only talks about not using 'sp_, another reason is that they may come up with the same name as your procedure on a future release.

Although I have not tested it properly, it seems that one should name his/her stored procedures as unique as possible for the maximum performance, starting from the first character.

I don't see how naming your stored procedures with the same prefix would affect performance at all. It has always been the norm in places I have worked to have a common procedure prefix as part of the naming standards.

Were any tests done on that?

I haven't done any tests my information comes from BOL.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

HI

Check out the below link

http://www.sqlservercentral.com/Forums/Topic8041-65-1.aspx

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

MannySingh SSChampion Points: 12465 More actions · Answer 5

Well i think everybody should start using the schema based Sepration Procedures for all objects. that will also be helpful in distinguishing the Objects and relate them to specific criterias.

Maninder
www.dbanation.com

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

I would recommend as a MINIMUM that all the stored procs you create be abbreviated usp_ for "user stored procedure." Not only does that avoid the sp_ issue but it keeps your stored procs seperate.

Clive Chinery SSCrazy Points: 2563 More actions · Answer 7

RonKyle (7/7/2008)
I would recommend as a MINIMUM that all the stored procs you create be abbreviated usp_ for "user stored procedure." Not only does that avoid the sp_ issue but it keeps your stored procs seperate.

That is an excellent idea. I have extended it to use asp_ for administrator only stored procedures that the user will never use.

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

Note: 2 year old thread.

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

Clive Chinery SSCrazy Points: 2563 More actions · Answer 9

Maninder S. (7/3/2008)
Well i think everybody should start using the schema based Sepration Procedures for all objects. that will also be helpful in distinguishing the Objects and relate them to specific criterias.

I agree and I would like to use domains, but I find resistance to adopting such a new concept.

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 10

Pardon me for jumping on a dormant thread, I came across this while researching naming conventions. Regarding the prefix "sp" (with or without the underscore), doesn't the sp stand for "System Procedure" and not "Stored Procedure"? Which would essentially be a vote against that prefix for user defined procedures used in applications.

Just curious.

Thanks,

--Jim

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

This is the first time I have seen where an old thread (more than 3 yrs) get some interest. 🙂

Check this article out[/url]. The second part of it shows the main reason why sp_ is costy and sp is not.

-Roy

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

I know it's several years old but that's still a good article, Roy. They should rerun it now and again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 13

Thanks Jeff. That was my second article for SSC. 🙂

-Roy