May 12, 2010 at 10:41 am
this is for sql 2000. many articles that i read advices to use dbo in front of a proc being executed to avoid recompilation due to eventclass 3 (which is deferred name resolution).
for example: execute dbo.someproc
Questions
1. If the sql user executing the above statement has dbo owner rights, is that still require to qualify the proc with dbo?
2. how about the sql objects inside that someproc? like tables, views, .
Do they also need to be qualify with dbo in front to reduce the compilation?
May 12, 2010 at 11:34 am
As a good practice ALL objects should be qualified by owner. This helps with compilations as well as executions. The DB engine doesn't have to think about who owns the object. In answer to your first question, it isn't so much about rights as it is ownership. When you try EXEC sprocname it doesn't know if you mean EXEC mydbuser.sprocname or EXEC dbo.sprocname so it has to look for the first. It doesn't take a lot of time but it does take some. As far as the second question, I'm not sure, but I would qualify every object anyway. My guess is that it could cause a recompile because a reference to tablename could be dbo.tablename and mydbuser.tablename.
CEWII
May 12, 2010 at 1:50 pm
I know this is for 2000 - but schema qualifying all objects is a recommended practice on SQL Server 2005/2008. There is an issue with not schema qualifying the objects that will cause multiple entries for the exact same query to generate multiple plans - one for each user executing the code.
Since it is recommended for those versions, I would recommend getting in the habit now before you upgrade.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply