Solved

Stored Procedure

Posted on 2009-07-12
10
246 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 80
Email Notifications for SQL 2005 9 37
Help Required 2 46
Clone table from one server.database to another server.database 24 49
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 I will describe the Backup & Restore 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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