Solved

Stored Procedure

Posted on 2009-07-12
10
244 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 67
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 71
Passing value to a stored procedure 8 107
kill process lock Sql server 9 64
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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

828 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