Qualified table names

I have a quick question on how to qualify table names as it relates to
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?

If you are user dwuser1, and you have CREATE TABLE permissions, and
you say:

CREATE TABLE dw_stage_1 (a int NOT NULL)

The full qualification for that table will be dwuser1.dw_stage_1. You
cannot refer to the table as dbo.dw_stage_1. As dwuser1 you can refer
to the table as dw_stage_1 and dwuser1.dw_stage_1. All other users,
including dbo, must refer to ut as dwuser1.dw_stage_1.

If you then log in as sa or any other login that map to dbo and say

CREATE TABLE dw_stage_1 (a int NOT NULL)

again, you have now created to dw_stage_1. As dbo you can refer to the
table as dbo.dw_stage_1 or dw_stage_1 only, and so can all other users
except for dwuser1, who must use dbo.dw_stage_1, since his only table
is ahead in the seatch path.

Best practice recommended my Microsoft is to refer to the table as
dbo.dw_stage_1. This is particularly important for loose SQL statements,
since SQL Server then can skip the search for user.dw_stage_1. They
say that this is also good in stored procedure, but in my opinion,
the dbo. becomes a four-letter line noice in a procedure that is owned
by dbo.

As for when to use objects not owned by dbo - beats me. I say, keep it
simple and only use dbo.

(In SQL 2005 where users and schema are separated, it's another story.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp