Lesson Learned #190: SQL function will not be executed remotely

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!