Remote Scan when updating using functions

Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?

UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = '123'

UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = number
WHERE accounts = '123'

The execution plan is as below:

The table does have a nonclustered unique index for the accounts column, but ISNULL() is not used on the accounts column and the index should still be usable. Am I missing anything here?

Am I missing anything here?

Not really. You are discovering that distributed query processing has lots of tricky limitations like this.

So the rule of thumb for using Linked Servers is to always use passthrough queries if you want remote execution.

exec (
'
UPDATE database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = ''123''
'
) at LINKEDSERVER1

This is just a guess, it's likely that there's just not a transformation that will allow ISNULL(number,0) to be pushed across to the remote server.

StackExchange Database Administrators Q#282429, answer score: 7