SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure naming prefix


Stored procedure naming prefix

Author
Message
l0n3i200n
l0n3i200n
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 36
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" ?
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4356 Visits: 7195
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.

'SP' should be fiine.
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 167
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.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4356 Visits: 7195
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
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1885 Visits: 409
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
MannySingh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1123 Visits: 787
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
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2665 Visits: 3492
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
Clive Chinery
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 154
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86382 Visits: 45232
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
Clive Chinery
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 154
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search