Re: newbie, querie help/advice
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 01/26/05
- Next message: Aaron Weiker: "Re: What exactly is a query plan (or a SQL plan for that matter)"
- Previous message: ap: "Using variable within a query"
- In reply to: Tim Mavers: "Re: newbie, querie help/advice"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 Jan 2005 17:16:21 +0100
> user1 is the owner of mytable (user1.mytable)
> user2 is the owner of mytable (use2r.mytable)
> user3 has read access to both tables.
>
> user3 executes code "select dbo.name from dbo.mytable"
First, above query will fail because you qualify the column name with dbo, and you didn't alias the
table name to dbo in the query. For the query to run, you would have to say:
SELECT dbo.name
FROM dbo.mytable AS dbo
In above, the column alias doesn't have anything to do with the owner, it might as well have been:
SELECT t.name
FROM dbo.mytable AS t
Second, above query will fail in run-time unless there also exist a table named "mytable" owned by a
user named dbo. Your sample code only showed two tables called "mytable", one owned by "user1" and
the other by "user2".
Dbo is a user that exists in every database, the database owner. Most people has dbo as owner of all
objects.
> I don't know why you would want multiple tables of the same name in the same database (even
> through they are owned by different users). I just seems messy.
True. In many cases you don't want that. Consider next version, where owner becomes schema. Where
schema is just a name. An object is still owned by someone, but the owner isn't longer part of the
object name. In this case, you might have schemas and names like:
Customers.Adresses
Suppliers.Adresses
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "Tim Mavers" <webview@hotmail.com> wrote in message news:j5idnejFENj7M2rcRVn-pw@giganews.com... > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:uhNWm1vAFHA.208@TK2MSFTNGP12.phx.gbl... >> It is the owner of the object (becomes "schema" in next version). It is considered good practice >> to qualify object name with owner and can also improve performance. > > I understand that you can have multiple objects in the same database with different owners (why I > have no idea, but I understand it is allowed). But what is the purpose of using dbo.? For > example: > > user1 is the owner of mytable (user1.mytable) > user2 is the owner of mytable (use2r.mytable) > user3 has read access to both tables. > > user3 executes code "select dbo.name from dbo.mytable" > > What the heck happens here? It just seems very sloppy to me. I don't know why you would want > multiple tables of the same name in the same database (even through they are owned by different > users). I just seems messy. > > > >
- Next message: Aaron Weiker: "Re: What exactly is a query plan (or a SQL plan for that matter)"
- Previous message: ap: "Using variable within a query"
- In reply to: Tim Mavers: "Re: newbie, querie help/advice"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
