<ji**********@msn.com> wrote in message
news:ac**************************@posting.google.c om...
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