SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Execution


Stored Procedure Execution

Author
Message
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 Visits: 2204
Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.


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.
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4674 Visits: 2741
UMG Developer (6/9/2010)
Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.


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.


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.
DLathrop
DLathrop
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 219
Yes, 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.

David Lathrop
DBA
WA Dept of Health
DLathrop
DLathrop
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 219
Full 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.

David Lathrop
DBA
WA Dept of Health
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 Visits: 2204
DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.


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?
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5241 Visits: 3648
UMG Developer (6/9/2010)
DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.


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?


That's news to me to.
DLathrop
DLathrop
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 219
Oops, I checked. The two part schema.object 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.]

David Lathrop
DBA
WA Dept of Health
itsme.mittal
itsme.mittal
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 3
I have used the same code but i have return as 5 as result

But i did not execute whole query in batch i have execute it individual

would you please reply me for it
Aparna-1
Aparna-1
Mr or Mrs. 500
Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)

Group: General Forum Members
Points: 581 Visits: 601
Good 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.
steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 227
The explanation is wrong.
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


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search