index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.
I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.
|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:

>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
>)
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
>)
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)