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?