views across databases

[jimstallings]

Hi,
I am trying to locate documentation on using views across databases.
We have serveral databases broken down by application, some shared
databases and some location specific databases supporting multiple
facilities using the applications. The DBA for our client is the one
that set up the topology of these databases but I don't understand the
performance considerations of having views in an applictation database
referencing tables in the shared databases. Is the enitre table
copied to tempdb when the view is referenced? How does indexing in
the containing database affect access through the view? These are
some of the questions I would like see documenation on.

Thanks,
Jim

[Simon Hayes]

<ji**********@m sn.com> wrote in message
news:ac******** *************** ***@posting.goo gle.com...

Hi,
I am trying to locate documentation on using views across databases.
We have serveral databases broken down by application, some shared
databases and some location specific databases supporting multiple
facilities using the applications. The DBA for our client is the one
that set up the topology of these databases but I don't understand the
performance considerations of having views in an applictation database
referencing tables in the shared databases. Is the enitre table
copied to tempdb when the view is referenced? How does indexing in
the containing database affect access through the view? These are
some of the questions I would like see documenation on.

Thanks,
Jim

Essentially, a view has no physical existence, and is purely logical - when
you execute a query which references a view, the view name is replaced with
its definition (it's 'expanded'), and then the query is parsed and executed.
That means that the query optimizer will look directly at the base tables in
the other database, and consider indexes on them as usual. There's no
special reason why tempdb would be used, unless it's needed anyway in the
query, perhaps to sort a large result set.

If you have an indexed view (MSSQL 2000 only), then the situation is rather
different - the view then does exist physically (at least in the form of
indexes), and the optimizer will look at the view's indexes rather than go
to the base tables.

Any issues are probably more to do with management (is the other database
always there?) than performance. Although if the view points at a database
on a different server, then there could easily be significant performance
implications.

See "Creating a View" and "Creating an Indexed View" in Books Online for
more details.

Simon

[jimstallings]

>

Essentially, a view has no physical existence, and is purely logical - when
you execute a query which references a view, the view name is replaced with
its definition (it's 'expanded'),

Hi Simon,
Thanks for taking the time to answer but what I am trying to find out
is a little deeper I think. I understood that views were just stored
queries when you were working within a database. What I was looking
for was something that described in a more definitive detail how the
fact that the view is in a different database "changes" the rules. Is
there an increased cost in accessing the index pages becuase you are
crossing database boundaries? Obviously since the databases are in
separate files, the first access will require disk I/O. Is the memory
shared between all databases in a seamless manner. I have read the
sections you mentioned as well as anything else in books online but
have not found this level of detail.

jim

[Simon Hayes]

ji**********@ms n.com wrote in message news:<ac******* *************** ****@posting.go ogle.com>...

Essentially, a view has no physical existence, and is purely logical - when
you execute a query which references a view, the view name is replaced with
its definition (it's 'expanded'),

Hi Simon,
Thanks for taking the time to answer but what I am trying to find out
is a little deeper I think. I understood that views were just stored
queries when you were working within a database. What I was looking
for was something that described in a more definitive detail how the
fact that the view is in a different database "changes" the rules. Is
there an increased cost in accessing the index pages becuase you are
crossing database boundaries? Obviously since the databases are in
separate files, the first access will require disk I/O. Is the memory
shared between all databases in a seamless manner. I have read the
sections you mentioned as well as anything else in books online but
have not found this level of detail.

jim

As far as I know, the rules don't change - the object name is
resolved, and the fact that the table is another database doesn't
matter. (Assuming no complications arising from cross-database
ownership chains, permissions, remote databases, databases set to
auto-close etc.) I quickly skimmed through chapter 15 of Inside SQL
Server 2000 (the bible of MSSQL internals) which discusses the query
processor, and while I may have missed something, I didn't see
anything to suggest that querying a table in another database is in
any way different from querying a table in the current database (since
the view is, as mentioned, expanded anyway).

If you're looking for 'low-level' information on any part of MSSQL
internals, then I highly recommend Inside SQL Server 2000.

Simon