SQL Server – Remote Scan When Updating Using Functions

execution-planlinked-serversql serversql server 2014

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:
ExecutionPlan1

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?

Best Answer

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
Related Question