When you query a stretch database, the SQL Server Database Engine runs the query against the local or remote database depending on data location. This is completely transparent to the database user. When you run a query that returns both local and remote data, you can see the Remote Query operator in the execution plan. The following query returns all rows from the stretch T1 table:
USE Mila; SELECT * FROM dbo.T1;
As expected, it returns five rows:
id c1 c2----------- -------------------- -----------------------2 Manchester United 2018-02-02 00:00:00.000 4 Juventus Torino 2018-02-25 00:00:00.000 5 Red Star Belgrade 2018-02-25 00:00:00.000 1 Benfica Lisbon 2018-01-15 00:00:00.000 3 Rapid Vienna 2018-01-28 00:00:00.000
You are surely much more interested in how the execution plan looks:
Execution plan for query with stretch tables
You can see that the Remote Query operator...