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

Qualifing table names with dbo

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 db
o
do anything at all, especially if the user that request the query to execute
is not the dbo simply a user with read only permissions?Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> 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 execute is not the dbo simply a user with read only
> permissions?
Objects in a database can be referred to with three-part names on the
form catalog.schema.object.
In SQL Server "catalog" is the same as "database". Up to SQL 2000 "schema"
was the same as owner, but in SQL 2005 owner and schema have been separated.
A schema can be thought of as a namespace, so that different groups that
work in the same database can use different namespaces, so they don't
get name clashes.
In SQL 2000, each user has a default schema which is equal to the user
name - obvious since schema and owner is the same. If the user "fred" says
"SELECT * FROM tbl", SQL Server first looks up his default schema, so
if there is a table fred.tbl, this is what he will get. If there is not,
SQL Server moves to the default schema for the database, which is dbo.
By prefixing with dbo, "SELECT * FROM dbl.tbl", fred makes an unmabiguous
reference.
In practice, all objects in a database are owned by dbo in 99% of the
database world-wide, so dbo becomes a bit redudant. But as I discussed in
another post today, there can be a performance benefit, if you
specify the schema.
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|||I think he means that dbo as the default database owner does not need to be
used, but should be for performance reasons.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jim Abel" wrote:
> I'm still unclear
> Please elaborate on what you mean by "if dbo exists" do yo mean if there i
s
> a dbo assigned to the database specifically as on of the users? and if one
> exists then how does it help if the query is requested by say the asp.net
> user that only has read access to the tables in the query?
> "Rajesh" wrote:
>|||Would adding dbo to db on a single server really make a difference in
performance?
(2000 or 2005)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Rajesh" wrote:
> 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 am very new to SQL Server but I thought dbo (DataBase Owner) always exists
,
it cannot be deleted.
Quote from MSDN:
========================================
======
The dbo user cannot be deleted and is always present in every database.
========================================
======
However, when to use it...I find very confusing.
T
"Rajesh" wrote:
> 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'm still unclear
Please elaborate on what you mean by "if dbo exists" do yo mean if there is
a dbo assigned to the database specifically as on of the users? and if one
exists then how does it help if the query is requested by say the asp.net
user that only has read access to the tables in the query?
"Rajesh" wrote:
> 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:
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> 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.
But the order of magnitute of this cost for it to be considered a
"performance issue", or to make dbo prefixing a performance "best practice".
David|||If the sp's are called by multiple users concurrently or many times a second
the lack of owner qualification can have a huge performance impact. There is
no question this is more than just a best practice. It has definite
implications if not done.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RIY3lcKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
> David
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
I should have included a reference to this KB article:
http://support.microsoft.com/kb/q263889/.
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|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23NQ3YDdKGHA.500@.TK2MSFTNGP15.phx.gbl...
> If the sp's are called by multiple users concurrently or many times a
> second the lack of owner qualification can have a huge performance impact.
> There is no question this is more than just a best practice. It has
> definite implications if not done.
>
What scenario are you claiming has a "huge performance impact"? Referencing
objects inside stored procedures? The objects are only resolved at query
parse/compile time, not on every call. IMO, basic best practices wrt shared
SQL eliminate any significant impact with object name resolution.
David