I know that a heterogeneous query joining tables from two different servers
has performance penalties but is the same true when joining tables from two
different databases on the same SQL Server 2000 instance?

We are looking at setting up a Data Warehouse using DTS on a SQL Server box
and I'm wondering about the best way to logically set it up; i.e. one big
honking db or several dbs determined by some logical organization. With the
latter there will still be some queries that would need data from more than
one db and I'm wondering if that will have worse performance than if they
were all in one db.

I thought that was the case in older versions of SS, but I couldn't find
anything in 2000's BOL that indicated a problem with that.

TIA

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:34ku1qF4btv2uU1@individual.net...
>I know that a heterogeneous query joining tables from two different servers
> has performance penalties but is the same true when joining tables from
> two
> different databases on the same SQL Server 2000 instance?
>
> We are looking at setting up a Data Warehouse using DTS on a SQL Server
> box
> and I'm wondering about the best way to logically set it up; i.e. one big
> honking db or several dbs determined by some logical organization. With
> the
> latter there will still be some queries that would need data from more
> than
> one db and I'm wondering if that will have worse performance than if they
> were all in one db.
>
> I thought that was the case in older versions of SS, but I couldn't find
> anything in 2000's BOL that indicated a problem with that.
>
> TIA
>
>

Personally, I'm not aware of any performance impact - certainly, I've never
seen any from doing this. If one database is updated frequently, then that
could be a problem, but it's probably unlikely in a data warehouse.

The data model and management issues such as backup/restore and security
would normally be the main issues to consider when deciding between the
implementation options.

Simon