I do believ the point is the fact that if you create a user defined sp with sp_ in master and execute it from any DB it works right.
But if you create a sp with sp_ with the same name in a user db as the name given to the one in master then it will execute the local one and not the one in master.
Which based on SQL BOL
quote:
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.
This should not bee the case, the one in master should be executed.
However, if you create an item in your database named sp_help the one in master will be executed no matter what you do.
The key is in the next statment in BOL
quote:
If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
In other words if the name is the same as a system defined stored procedure in master and not a user one then masters copy will override the code.
Now, if you run
sp_MS_marksystemobject sp_mysp
against the Procedure you created in the master database and then execute mydb..sp_mysp it will still run the local db copy even thou the master copy does state type is system.
Here is the trick, restart the server now and when comes back up you will find running mydb..sp_mysp will now execute the master copy instead.
Ultimately I believe when the server starts it creates a list in memory of all the system procedures in master, when you call a procedure of that name the list will trigger the master copy to be executed instead of any local copy, even if you fully qualify.
If the item is not in the list tht was create at startup then the local copy is applied. The advatange is that an system SP in master can be accessed quickly without needing to qualify.
Furthermore, if you test further you will find that any other first 3 characters as the procedure name will not work, even if you name the procedure spmysp and make a system object then reboot it will still run the local copy.
So at load the server must only load procedures prefixed sp_ which does not affect performance except at load time since it will have to compare the procedures to find those. I also believe that unless the query manager does a left 3 on the running sp everytime (which it may) then your procedure will be checked against the list everytime to make sure not in the list.
Either way it would be (IMHO) safe to assume that the performance hit is the same either way, except you don't have to worry if your object name is stepped on by something in master.
In sumation I think the search order is actually more like this.
1) Is SP_ item matching name in master list of system procedures created at time of SQL Server Start.
2) Is SP_ procedure in fully qualified path.
3) If SP is not called via the fully qualified path does SP_ exist in current database.
4) If not found in any of the previous does it exist in the master DB as a user type or as system type (newly changed to system type after SQL Server startup).
I tested under SQL 7 SP4 and SQL 2000 SP2.
Note: I choose to prefix ip_ (which is short individual procedure, picked this up from someone else), unless I am creating an SP in master for code reusability.
Edited by - antares686 on 11/12/2002 07:04:24 AM