Solved

Stored Procedure

Posted on 2009-07-12
10
240 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
  • 5
  • 4
10 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 125 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 125 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
 

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 125 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 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 125 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 125 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with a query 4 44
SQL Where not exists in same table 3 41
SQL help 5 47
Why do I get extra rows when I do inner join? 12 34
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now