Solved

Stored Procedure

Posted on 2009-07-12
10
249 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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