Introduction
I have seen in the forum that many of the members are either writing sample stored procedure in their questions / answers with a name starting as "SP_". Its not a good practise and it would have a major hit on the performance of their Database. So just thought of warning them with this article :)
Why Shouldn't we?
In Microsoft SQL Server, the system stored procs would be prefixed with "SP_". That said, if we prefix "sp_" in our user-defined stored procedure it would bring down the performance because SQL Server always looks for a stored procedure beginning with "sp_" in the following order:
1) FIrst its looks into the Master database,
2) If it is not able to find within "Master DB" then it would start searching the stored procedure based on the fully qualified name provided,
3) If it still not able to find it then it would search for the stored procedure using dbo as the owner, if one is not specified.
So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed.
Quick Sample
For exampe: Lets say that by mistake you have named one of your user defined stored procedure as "sp_help" within our database!
Create proc sp_help
as
Select * from dbo.empdetails
Now when you try executing the stored procedure using the below script you would realize that it has actually called the sp_help system stored proc of Master DB and not our SP.
Exec sp_help
Summary
Bottomline is "Never name your stored procedure with a prefix SP_ unless or until you are going to place it within the Master DB!". Hope this helps!!
|
| Author: vannalu.prabhulokesh 07 Jan 2007 | Member Level: Bronze Points : 0 |
Good Article.
|