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:

You can see that the Remote Query operator operates with an Azure database and that its output is concatenated with the output of the Clustered Index Scan that collected data from the local SQL Server instance. Note that the property window for the Remote Query operator has been shortened to show only context-relevant information.

What does SQL Server do when only local rows are returned? To check this, run the following code:

SELECT * FROM dbo.T1 WHERE c2 >= '20180201'; 

The query returns three rows, as expected:

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

And the execution plan is shown as follows:

The plan looks good; it checks only the local database and there is no connection to Azure. Finally, you will check the plan for a query that logically returns remote data only. Here is the query:

SELECT * FROM dbo.T1 WHERE c2 < '20180201'; 

You will again get the expected result:

id          c1                   c2
----------- -------------------- -----------------------
1           Benfica Lisbon       2018-01-15 00:00:00.000
3           Rapid Vienna         2018-01-28 00:00:00.000

Here is the execution plan:

You probably did not expect both operators here only Remote Query should be shown. However, even if the returned data resides in the Azure SQL database only, both operators should be used since data can be in an eligible state, which means that it has not yet been moved to Azure!

Querying stretch tables is straightforward; you don't need to change anything in your queries. One of the most important things about stretch databases is that the entire execution is transparent to the user and you don't need to change your code when working with stretch tables.

However, you should not forget that enabling Stretch DB can suspend primary key constraints in your stretched tables; uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.