Can not use Query Hints inside Azure SQL

Question

Hey,

In an Azure project we are querying two remote databases and then joining them using an INNER JOIN.

The query takes rather long and analysing the query plan with SQL Management Studio showed that the problem is that a lot of data is transmitted from the two remote queries and then locally joined.

We now want to force the query to execute the join on the remote SQL server and then only transmit the resulting data.

For this I understood that the query hint "INNER REMOTE JOIN" can be used. But when trying to do so, the SQL server says that its current version does not support the REMORE parameter.

I understood it that way, that the Azure SQL always runs on the most current stable version of SQL Server, so why cant I use the Hint?

Answer 1

The hint cannot be used on Azure SQL Database because you query tables on another database or logical server using elastic queries on Azure SQL. Cross database (server) queries are only allowed via elastic queries. Basically, you query tables locally with Azure SQL Databases. The Remote Join Hint is for example used when you use queries across linked servers, but linked servers are not supported on Azure SQL.

When you need cross database queries maybe Azure Managed Instance (MI) is a better option, and MI allows the creation of linked servers. You may also consider to consolidating many databases in one Azure SQL database assigning them separate schemas within the Azure SQL database.