Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure

Posted on 2009-07-12
10
Medium Priority
?
252 Views
Last Modified: 2012-05-07
Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
is this statment correct?
0
Comment
Question by:hazemfadl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24833682
>> Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database. is this statment correct?

sp_ prefix will be done for system created stored procedures and that too particularly in master database.
Hence it is a default or recommended practice to create procedures in master database to prefix it with sp_

That will help you easily identify whether that procedure was system generated or for any critical purposes in master database from the user created stored procedures.

Hence you would have heard that advice or recommendation from others and hence that is actually correct
0
 

Author Comment

by:hazemfadl
ID: 24833776
I don't use Master DB i have my own DB
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24833794
>> I don't use Master DB i have my own DB

Then kindly use some other naming convention to identify your set of stored procedures from system stored procedures.

Instead of sp_ in small letters, use SP_ in BLOCK letters so that it would be easier to identify for other users.
Hope this makes sense.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hazemfadl
ID: 24833799
if i use sp_ prefix  is this affect on performance?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24833801
As I mentioned earlier, the only fact is it would be easier to identify that procedures with prefix sp_ is a System defined procedure.

Not to do anything with performance and any other factors..
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24834890
>>if i use sp_ prefix  is this affect on performance?<<
There are two reason why you do not want to use sp_ for stored procedures:
1.  Performance.  See this from a Patterns & Practices article on MSDN:
Designing Data Tier Components and Passing Data Through Tiers
http://msdn.microsoft.com/en-us/library/ms978496.aspx
And in particular this quote:
Note   Do not preface your stored procedure names with sp_, because doing so reduces performance. When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name.

2. Readability.  It is easier to distinguish your user defined Stored Procedures from system Stored Procedures.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24836492
>> http://msdn.microsoft.com/en-us/library/ms978496.aspx

Thanks for the reference acperkins.
And I haven't heard about this one before.
0
 

Author Comment

by:hazemfadl
ID: 24838103
Is there exist difference between sp_ small letter and SP_ capital letters or I ignore using any one of them
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24838263
>> Is there exist difference between sp_ small letter and SP_ capital letters or I ignore using any one of them

It will help in identifying whether this was system generated or user created one.

Using SP_ will minimize the performance impact as it is checking for sp_ in the name of the stored procedure.
But if the database is case insensitive, then again it becomes an issue impacting performance.

Hence it is advised not to prefix user defined procedures with either sp_ or SP_.
0
 

Author Closing Comment

by:hazemfadl
ID: 31602544
thanks very much
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question