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

Qualified table name syntax

Im trying to write a generic data access layer that supports SQL CE and Im wondering if any type of schema qualifier can be placed in front of a table name when executing a sql statement.

I've tried soemthing like this

select * from dbo.Account

I get this error,

The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = account ]

It doesnt make really make sense to include a qualifier for sql ce but I just wanted to make sure that there wasnt some other syntax that I wasnt aware of.

Thanks.

Nope, as I describe in the book, the SQL engine for "SQL Server" Compact Edition is not SQL Server--it uses a subset so the concept of "ownership" or "schemas" does not exist--it simply confuses the little engine. See www.hitchhikerguides.net FMI.|||thanks for confirming my thoughts. I'll check out the guide.

Qualified Joins

I need a query that compares two tables with times in them and returns only
the common records with the lowest time.
Table1
recordID ArrivalTime
12345 12:01am
12346 12:30am
12347 12:45am
Table2
recordID ArrivalTime
12345 12:03am
12346 12:29am
12347 12:44am
The result should be
recordID ArrivalTime
12345 12:01am
12346 12:29am
12347 12:44am
Can someone point me in the right direction please?Hi Dave,
SELECT recordID,MIN(ArrivalTime)
FROM
(
SELECT recordID,ArrivalTime
FROM TABLE1
UNION
SELECT recordID,ArrivalTime
FROM TABLE2
) SubQuery
GROUP BY recordID
--OR
SELECT
recordID ,
CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
ELSE T2.ArrivalTime END AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
HTH, jens Suessmeyer.|||The 2nd query doesn't handle nulls...
SELECT
COALESCE( T1.recordid , T2.recordid ) AS recordid
, (CASE
WHEN T2.ArrivalTime IS NULL OR T1.ArrivalTime < T2.ArrivalTime ) THEN
T1.ArrivalTime
ELSE T2.ArrivalTime
END) AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131981403.240416.108000@.g47g2000cwa.googlegroups.com...
> Hi Dave,
>
> SELECT recordID,MIN(ArrivalTime)
> FROM
> (
> SELECT recordID,ArrivalTime
> FROM TABLE1
> UNION
> SELECT recordID,ArrivalTime
> FROM TABLE2
> ) SubQuery
> GROUP BY recordID
>
> --OR
> SELECT
> recordID ,
> CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
> ELSE T2.ArrivalTime END AS ArrivalTime
> FROM TABLE1 T1
> FULL OUTER JOIN TABLE2 T2
> ON T1.recordid = T2.recordid
>
> HTH, jens Suessmeyer.
>