part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0013_01C3E81F.1B25ACF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
To create an indexed view, you must use the WITH SCHEMABINDING OPTION. For
this option, you need to use two-part naming, which precludes their use
outside of the database in which they are created.
A stored proc can use 1 - 4 part naming, allowing you to call a stored proc
from outside the database and even outside the server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Patrick Ikhifa" <ispi@.gte.net> wrote in message
news:eIP3MPC6DHA.2264@.tk2msftngp13.phx.gbl...
Hi All,
part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.
--=_NextPart_000_0013_01C3E81F.1B25ACF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To create an indexed view, you must =use the WITH SCHEMABINDING OPTION. For this option, you need to use two-part =naming, which precludes their use outside of the database in which they are created.
A stored proc can use 1 - 4 part =naming, allowing you to call a stored proc from outside the database and even outside the =server.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Patrick Ikhifa"
--=_NextPart_000_0013_01C3E81F.1B25ACF0--
No comments:
Post a Comment