HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Indexed views on double linked server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
viewsindexeddoubleserverlinked

Problem

A server SERVER_A has some data.

A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this:

CREATE view myview as
select * from 
openquery ( SERVER_A, select .... )


A server SERVER_C defines B as a linked server.

I have full access to SERVER_C, I can ask for some changes to SERVER_B, but not to SERVER_A. All my applications will access server SERVER_C, and need the data from SERVER_A. The views on SERVER_B are quite complex, and they cause timeout before returning anything.

I think that to improve the performance on these queries I need to turn the views on SERVER_B into "indexed views". To index a view, I must create a clustered index.

Questions:

1) Where should I define the clustered index? Should it be at SERVER_B, or I can somehow define it on SERVER_C?

2) Will the use of an indexed view on SERVER_B affect in any way the performance of SERVER_A even when the view is not being used?

Solution

You can't create an Indexed view on linked server table.

According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx
"The view must reference only base tables that are in the same database as the view."

Context

StackExchange Database Administrators Q#80125, answer score: 5

Revisions (0)

No revisions yet.