As mentioned earlier, queries with stretch-enabled tables return both local and remote data by default. You can manage the scope of queries by using the system stored procedure sys.sp_rda_set_query_mode to specify whether queries against the current Stretch-enabled database and its tables return both local and remote data, or local data only. The following modes are available:

• LOCAL_AND_REMOTE (queries against Stretch-enabled tables return both local and remote data). This is the default mode.
• LOCAL_ONLY (queries against Stretch-enabled tables return only local data).
• DISABLED (queries against Stretch-enabled tables are not allowed).

When you specify the scope of queries against the Stretch Database, this is applied to all queries for all users. However, there are additional options at the single query level for an administrator (member of the db_owner group). As administrator, you can add the query hint WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) to the SELECT statement to specify data location. The option REMOTE_DATA_ARCHIVE_OVERRIDE can have one of the following values:

• LOCAL_ONLY (query returns only local data)
• REMOTE_ONLY (query returns only remote data)
• STAGE_ONLY (query returns eligible data)

The following code returns eligible data for the T1 table:

USE Mila; 
SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = STAGE_ONLY); 

Here is the output:

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

Run this code to return data from the T1 table already moved to Azure:

SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = REMOTE_ONLY); 

Here is the output:

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

Finally, this code returns data in the T1 table from the local database server:

SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = LOCAL_ONLY); 
 

As you expected, three rows are returned:

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

By default, you can't update or delete rows that are eligible for migration or rows that have already been migrated in a Stretch-enabled table. When you have to fix a problem, a member of the db_owner role can run an UPDATE or DELETE operation by adding the preceding hint and will be able to update data in all locations.