Today, I worked on a very interesting case where our customer is running the following query "Select top 20000 *,ROW_NUMBER() over (partition by [field1] order by field2) from dbo.[RemoteTable] that is taking too much time, where RemoteTable could be a table from Linked Server (available in OnPremise and Azure SQL Managed Instance) or Elastic Query (available in Azure SQL Database).
- Using Elastic Query, SQL Server will send remotely the following query: select * from dbo.[RemoteTable] and after receiving the rows, the function ROW_NUMBER algorithm will be apply in local.
- Using Linked Server, SQL Server will send remote the following query: select * from dbo.[RemoteTable] Order by Field1, Field2 and after receiving the rows, the function ROW_NUMBER algorithm will be apply in local
So, in this situation, using sp_execute_remote for Elastic Database Query and OpenQuery for Linked Server will be the best and faster solution, because the function will force query execution on the remote server.
Enjoy!
