Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored procedure naming prefix Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 3:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2008 4:03 AM
Points: 325, 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" ?
Post #527117
Posted Wednesday, July 2, 2008 4:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.

'SP' should be fiine.
Post #527136
Posted Thursday, July 3, 2008 12:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:33 PM
Points: 20, Visits: 157
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?
Post #527796
Posted Thursday, July 3, 2008 2:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.
Post #527849
Posted Thursday, July 3, 2008 1:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, 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



Post #528383
Posted Thursday, July 3, 2008 2:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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
Post #528408
Posted Monday, July 7, 2008 8:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:27 AM
Points: 802, Visits: 1,983
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.


Post #529325
Posted Thursday, July 15, 2010 4:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, 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.
Post #952955
Posted Thursday, July 15, 2010 4:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
Note: 2 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #952968
Posted Thursday, July 15, 2010 5:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, 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.
Post #953014
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse