SQLTeam.com | Weblogs | Forums

Indexed view using remote database

Dear Friends,

i have an requirement where i need to create a indexed view with a table which is available in different server. but i get an error as Remote access is not allowed from within a schema-bound object. I understand this problem.

but i want to create as it is. i can create the same table in the database where am creating the indexed view. but i do not want to do that. i need an solution on how to handle it.

so that i can save on lot such views

Sethu Murugan

I guess a part of the reason why you're creating the indexed view is for performance. If that's true, then try using OPENQUERY against the remote database as a part of your query instead of using an indexed view. A join to OPENQUERY over a linked server is a lot faster than trying to do a join over a linked server because the query of OPENQUERY executes on the remote server.

If that's not good enough and you have to have an indexed view, then you're probably out of luck. That's when I'd resort to using OPENQUERY to quickly create a preaggregated temp table and join to that.