SQL Server: efficient query with (double) linked servers I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version).
I have no control on server C, and I cannot contact its DBA.  I am not even sure that server C is Microsoft SQL Server, it might be Oracle.

On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as 
select * from 
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')


I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27


This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27


I considered using indexed views, but they cannot work with linked servers.
Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?
Since you know exactly the T-SQL you'd like to run on server 'B', why not just execute that query remotely, at server 'B', like this:

DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT t.c1
    , t.c2
    , t.c3
FROM OPENQUERY (SERVER_C, N''SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27;'') t;
';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;


That will cause server 'B' to query server 'C' with all the parameters you want, and should be about as quick as you can get.