Indexed Views that contains a linked server

Question

Indexed Views that contains a linked server

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323741 More actions · Answer 1

for an index to be created, it has to be on an object that exists inside the database; that way constraints can be enforced.

constraints cannot be created accross db boundaries.; so you could index a local copy of the data from the linked server, but not against the linked server's data itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 2

Is there a way to create a local table/View of the database tables in the other databases and have the local table updated when there are Insert/updates to the other DB tables. I'm hoping that I don't have to create triggers to do this, but if I have to then thats what A'll do. I'm hoping for a solution that will allow me to set it up once and have all the updated sent to the Local db. Thanks

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 3

One last thing. The reason I wanted to create a index or Primary key on the original view was because I have 4 divisions in our company that I query data from and to distinguish the divisions I have added a ProgramId to each query. The Key should contain the PatientId and ProgramId.

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 4

Could Replication help me out here?

I've never had to use replication at this point, so please bare with me. I'm starting to read up on the topic. It looks as if this may be the solution, correct me if I'm wrong please. If I'm understanding the topic so far. I should be able to create a Local table in the current Application db and set it up as a "Publisher". The db's I want to get the data from would be setup as "Distributors". Is this correct so far?

Questions on replication:

Can a query be created to decide what data is actually sent to the "Publisher", or does it require that all fields be sent over?

Can the local application Query against the "Publisher" table like any other table or view?

The remote sites (Distributors) will not need access to the replicated data as they will be querying against there local data. Will this be a problem?