Qualifing table names with dbo

if dbo exists then the sql server resolves the object immeidately
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:

> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx