InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:24:34 GMT20http://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxAnswer is Returned as 5 and not 7 and give back my pointWed, 11 May 2011 00:39:01 GMTitsmattahttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxreally good one.thanksMon, 09 Aug 2010 06:41:00 GMTyogenderhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxThe explanation is wrong.[quote]Explanation: "SQL Server uses the sp_ prefix to designate system stored procedures... A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead."http://msdn.microsoft.com/en-us/library/ms190669.aspx[/quote] Just because you create a procedure in master that is named sp_ does not make it a system stored procedure. I am still very angry at MS for taking away the ability to create system functions in 2k5.In the example the only reason the sproc in master is executed is that the local procedure was in a non-default schema.This is why people without Adventure Works were getting different results.Wed, 28 Jul 2010 05:42:56 GMTsteven.malonehttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxGood Question. I got it wrong. I did not see that the sp started with sp_.In my company we follow the standard of not using sp as starting letter for stored procs name and somehow missed to spot this one.Wed, 16 Jun 2010 19:47:33 GMTAparna-1http://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxI have used the same code but i have return as 5 as resultBut i did not execute whole query in batch i have execute it individualwould you please reply me for itTue, 15 Jun 2010 01:34:56 GMTitsme.mittalhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxOops, I checked. The two part [i]schema.object[/i] form of name is considered "fully qualified" for plan caching; you don't need to prefix names with the database.Sorry for sharing my confusion. [In some contexts "fully qualified name" means three or four part names.]Wed, 09 Jun 2010 17:28:40 GMTDLathrophttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]UMG Developer (6/9/2010)[/b][hr][quote][b]DLathrop (6/9/2010)[/b][hr]Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.[/quote]I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?[/quote]That's news to me to.Wed, 09 Jun 2010 15:57:40 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]DLathrop (6/9/2010)[/b][hr]Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.[/quote]I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?Wed, 09 Jun 2010 15:54:59 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxFull three part naming helps the query optimizer recognize and reuse queries in the execution plan cache. Queries with unqualified names, including those in stored procedures, have to be fully parsed and processed each time they execute because the name resolution is based on that session's user's default schema.So if you can use fully qualified names in your environment, it gives much better performance.If you need a test database, you might consider using another SQL Server instance, a virtual machine, or testing on "developer" workstations.Wed, 09 Jun 2010 15:50:35 GMTDLathrophttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxYes, you should definitely avoid using the sp_ prefix. Not just the performance hit, since it always checks the master and resource databases first. Also, the next version of SQL Server could have a new system stored procedure with the same name; you'ld be scratching your head for a long time to figure out why you application was suddenly failing.However, as a DBA, you can be comfortable using the system stored procedure, even in a develpment database. A programmer can't trip you up by leaving an sp_ stored procedures waiting for you to execute it with SA rights.Wed, 09 Jun 2010 15:29:37 GMTDLathrophttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]UMG Developer (6/9/2010)[/b][hr][quote][b]Nadrek (6/9/2010)[/b][hr]B) Always use three part naming, so you either execute: Adventureworks.Person.(otherprefix)ReturnSomethingor Master.dbo.(otherprefix)ReturnSomethingboth of which make it explicitly obvious what you're calling.[/quote]That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.[/quote]True, "best practices" need to be evaluated in relation to any given situation and set of constraints one is under. As the situation and the constraints change, so too do the "best practices" that best fit.Since I've often dealt with systems that do a reasonable percentage of cross-database, same-instance calls, three part naming across the board makes sense; if you move to differently named database sets on the same instance, you're going to have to do a lot of careful global search and replace anyway.Wed, 09 Jun 2010 11:01:10 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]Nadrek (6/9/2010)[/b][hr]B) Always use three part naming, so you either execute: Adventureworks.Person.(otherprefix)ReturnSomethingor Master.dbo.(otherprefix)ReturnSomethingboth of which make it explicitly obvious what you're calling.[/quote]That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.Wed, 09 Jun 2010 10:56:37 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote]Ambiguity is a playground of subtle bugs[/quote]I like it.Wed, 09 Jun 2010 10:51:46 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxReasons I recommend:A) Never have the first three letters of any stored user procedure be "sp_"B) Always use three part naming, so you either execute: Adventureworks.Person.(otherprefix)ReturnSomethingor Master.dbo.(otherprefix)ReturnSomethingboth of which make it explicitly obvious what you're calling. Ambiguity is a playground of subtle bugs.Wed, 09 Jun 2010 10:45:39 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxThanks - it is a good question, good reminder, and good discussion.Wed, 09 Jun 2010 09:53:52 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxInstead of Person.sp_ReturnSomething, create proc with dbo prefix and it returns 5.I got it wrong as I don't have Adventureworks DB. But, with testing it and getting right would be cheating myself.Good question. Everyday is a learning day.Wed, 09 Jun 2010 08:58:13 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxVery good question, thank you Jason.Creating a proc with name beginning with [b]sp_[/b] is very evil indeed. While there is a way to invoke the AdventureWorks' version of the proc by schema qualifying it, i.e. [code="sql"]exec Person.sp_ReturnSomething 5;[/code]returns desired [code="sql"]Result-----------5[/code]still beginning proc name with sp_ is ill advised for a very good reason.OlegWed, 09 Jun 2010 08:51:20 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxGreat question, My first thought was the correct answer, but then I noticed that the procedure in the Master db was not prefixed by a schema so I had to do some reading to see how that would be handled.I think the lesson here is never use sp in your stored procedure names and always qualify your calls.Wed, 09 Jun 2010 08:43:14 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]prashant.bhatt (6/9/2010)[/b][hr]Very Nice questionI am not sure but the query is returning me 5....I dont have the AdventureWorks db on the server...Tried using the test database with dbo schema.... may be this is why the results are different for me[/quote]Yes, if DBO is your default schema and you create the stored procedure in the DBO schema that returns 5, then that is the version of the stored procedure that will execute.Wed, 09 Jun 2010 08:27:53 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote]Avoiding uage of prefix sp_ improves the performance[/quote]When a stored procedure is executed using "sp_", SQL Server checks in the master first, as "sp_" is assumed to be reserved for a system stored procedure. Thus, the performance improvemnet would be caused by not having to go to the system to look first for the procedure.In this case, executing the procedure as written causes the process to be executed from the master (system) where the code is [quote]SELECT @Input + 2 AS Result[/quote]Wed, 09 Jun 2010 07:08:48 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxExecuting the code as written... won't it return 5? "Use Adventureworks" points the "EXECUTE sp_ReturnSomething 5" to Adventureworks and runs that proc. What am I missing this early in the A.M.?Wed, 09 Jun 2010 06:36:33 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxthis does not work as explained with SQL 2008 R2.sp or not, the local version of the stored procedure is executed first.Wed, 09 Jun 2010 05:27:32 GMTThierry Steenberghshttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxGood question Jason!Wed, 09 Jun 2010 04:45:48 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxVery Nice questionI am not sure but the query is returning me 5....I dont have the AdventureWorks db on the server...Tried using the test database with dbo schema.... may be this is why the results are different for meWed, 09 Jun 2010 02:27:20 GMTprashant.bhatthttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxAvoiding uage of prefix sp_ improves the performance.Wed, 09 Jun 2010 02:10:14 GMTvignesh 38804http://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]forjonathanwilson (6/8/2010)[/b][hr]Nailed it, because I assumed that "person" was not me. If I were "person" the answer would have been 5.[/quote]Or if the default schema of the user executing it is person.Wed, 09 Jun 2010 01:03:59 GMTMightyhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxI got wrong.Thought that it will give error the sp_ ...... procedure already exists if the procedure name alreay in master database.Wed, 09 Jun 2010 00:50:11 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspx[quote][b]ziangij (6/8/2010)[/b][hr]thanks, great question... got it wrong though :([quote]A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead. [/quote]i have read the microsoft link; was wondering if the user-defined proc doesn't ever run then what is the purpose of creating that proc ? why can't an error be thrown instead ?[/quote]1) the user defined proc can be run explicitly, perhaps in within an EXEC(string) used in a proc that built the command with the user name as argument. 2)Or if that particular user (joeFoo) runs sp_whatever, and his/her username is attached to another proc with the same name (joeFoo.sp_whatever), then THAT proc executes.Tue, 08 Jun 2010 23:13:19 GMTforjonathanwilsonhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxNailed it, because I assumed that "person" was not me. If I were "person" the answer would have been 5.Tue, 08 Jun 2010 23:08:12 GMTforjonathanwilsonhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxthanks, great question... got it wrong though :([quote]A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead. [/quote]i have read the microsoft link; was wondering if the user-defined proc doesn't ever run then what is the purpose of creating that proc ? why can't an error be thrown instead ?Tue, 08 Jun 2010 22:50:42 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxGreat question, thanks Jason!The old don't start your SP names with SP_I really wish I knew all of this 13 years ago... ;)Tue, 08 Jun 2010 22:00:48 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic934369-1419-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/Stored+Procedures/69600/">Stored Procedure Execution</A>[/B]Tue, 08 Jun 2010 21:59:34 GMTJason Shadonix