Indexed view using remote database

Question

Post reply

Indexed view using remote database

sethumurugan

SSC Enthusiast

Points: 172
More actions
March 1, 2020 at 4:53 am

#3729925

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

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

your only solution is to create it as a table is you need to create an index to it.

To create an indexed view you are required to use SCHEMABINDING on the view definition
to create a view with SCHEMABINDING all objects referenced need to local to the database where the view is created.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

frederico_fonseca wrote:

your only solution is to create it as a table is you need to create an index to it.

<li style="list-style-type: none;">
To create an indexed view you are required to use SCHEMABINDING on the view definition

<li style="list-style-type: none;">
to create a view with SCHEMABINDING all objects referenced need to local to the database where the view is created.

And if you are creating the table locally - then there is no reason to create an indexed view as you can just add the indexes you need directly to the local table.

This goes back to the problem with linked servers and how they are utilized. For small transactions - where SQL Server can properly identify the remote tables filters - and request from the remote server just those rows it can work well. As soon as SQL Server cannot do that it will either pull all rows from the remote table every time - and filter locally - or it will utilize a cursor and check each row one at a time.

In most cases - the data needed from the remote server is fairly static. That is - in most cases the data required is not current data and it can be extracted from the remote system and updated into a local table for reporting purposes. In those cases - either an SSIS ETL process, BCP or OPENQUERY across the linked server scheduled on a daily basis to update the local table(s) are a much better option. This provides the required data for reporting from local tables which can be optimized for the specific reporting requirements.

If the requirement is current data for reporting - then you really need to look at replication. Using replication to update the local tables provides for near real-time data and the ability to add indexes to the local tables to support the reporting requirements.

It really depends on the actual requirements that you are trying to meet.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Most people want to make indexed views for performance purposes which, of course, don't work on remote servers because of the schema binding issue.

If you don't really need it to be a view, consider using OPENQUERY, which runs on the remote server across the linked server and is a whole lot faster than just doing a query against the linked server especially if there's some criteria involved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)