Remote Update

Introduction

The Remote Update operator updates rows in a table or view on a remote server. It reads rows from its child, one by one, and sends them to the remote server for updating. If an error is returned from the remote server, then the transaction is aborted and the same error is raised locally.

Cases where Remote Update are used are, based on my observations so far:

• Using an UPDATE statement with a remote table or view as the target, if the execution plans needs to access at least one other object
• Using an UPDATE statement with an OPENDATASOURCE expression as the target, if the execution plans needs to access at least one other object.
• Using an UPDATE statement with an OPENQUERY expression as the target.
• Using an UPDATE statement with an OPENROWSET expression as the target, if the statement gets information from at least one other source, or if the remote object is specified as a query.

In these cases, a Remote Scan is used to find the rows to update on the remote server, and, after optional processing on the local side, the rows to be updated, along with the new column values, are sent using Remote Update.

SQL Server also supports several other scenarios to update data in remote objects, but in my tests, these use a Remote Query to send the entire update statement to the remote server, rather than fetching rows and then using Remote Update to update rows:

• Using an UPDATE statement with a remote table or view as the target and that accesses no other objects.
• Using an UPDATE statement with an OPENDATASOURCE expression as the target and that accesses no other objects.
• Using an UPDATE statement with an OPENROWSET expression as the target and that accesses no other objects, if the remote object is specified by object name.

If you find any cases where the Remote Update operator is used and that are not listed above, please let me know!

Visual appearance in execution plans

Depending on the tool being used, a Remote Update operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Remote Update operator is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.

Update row

The Update row action sends the key value(s) of the row to be updated to the Remote Object, in the form of a single “bookmark” column, plus the new values of the column(s) to be updated, as specified in the Predicate property. The “bookmark” column is named Bmknnnn, where nnnn is a four- or five-digit number that is unique within the execution plan. The content and data type of this column is not documented, but I assume that it is either the Row Identifier (RID) if the target table is a heap, or a concatenation of all clustered index columns otherwise. This column is returned from the remote server by the Remote Scan operator, and then passed to the Remote Update if the rows needs to be updated.

For an update in a remote table or view, the Remote Object property holds the object name (using three-part naming: database, schema, and table or view), whereas the server name is stored in the Remote Source property.

For an update in an OPENDATASOURCE expression, the Remote Object property holds the object name (using three-part naming: database, schema, and table or view). The Remote Source is set to “<UNKNOWN>”. I assume that the actual information (provider name and connection string) is stored in the internal execution plan, but not included in the export to XML.

For an update in an OPENQUERY expression, the Remote Object is the query passed into the OPENQUERY expression, whereas the server name is stored in the Remote Source property.

For an update in an OPENROWSET expression, the Remote Object is either the object name (using three-part naming: database, schema, and table or view), or the query passed into the OPENROWSET expression. The Remote Source is set to “<UNKNOWN>”. I assume that the actual information (provider name, plus either connection string or data source, user id, and password) is stored in the internal execution plan, but not included in the export to XML.

There is no property in the execution plan to expose which value from the input to use as the “Bookmark” column. All cases of Remote Update I have seen so far have this column as the first column in the input. So either the Remote Update operator requires that the bookmark column is always the first, or the internal execution plan does have this information, but it is not included in the export to XML.

Operator properties

The properties below are specific to the Remote Update operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Remote Update operator are marked with a *.

Property name	Description
Output List *	In all cases so far where I have looked at the Remote Update operator, the Output List property was empty in the XML and in the graphical execution plan. The operator still returns a row to its parent every time it is called, but it is an empty (zero columns) row. I don’t know whether this is a limitation of the operator, or whether I just haven't been able to construct the right test case yet.
Predicate	Maps columns from the input stream to the columns in the Remote Object.
Remote Object	The object (table or view name) on the remote server that needs to be updated, using three-part naming (database name, schema name, and table or view name). When Remote Update is used with an OPENROWSET or OPENQUERY target, the Remote Object property can also be the text of a query, that has to follow the rules for updateable views.
Remote Source	The name of the server where the rows have to be updated in the Remote Object. When Remote Update is used to update an OPENDATASOURCE or OPENROWSET expression, this property shows “<UNKNOWN>”. I assume that the actual information (provider name, plus either connection string or data source, user id, and password) is stored somewhere in the actual internal execution plan, but not included in the XML representation.

Implicit properties

This table below lists the behavior of the implicit properties for the Remote Update operator.

Property name	Description
Batch Mode enabled	The Remote Update operator supports row mode execution only.
Blocking	The Remote Update operator is non-blocking.
Memory requirement	The Remote Update operator does not have any special memory requirement.
Order-preserving	I assume that the Remote Update operator is fully order-preserving. However, since I have not been able to construct execution plans where the Remote Update operator returns actual data to its parent, so I have not yet been able to verify this assumption.
Parallelism aware	The Remote Update operator can only be used in serial execution plans.
Segment aware	The Remote Update operator is not segment aware.

Change log

(Does not include minor changes, such as adding, removing, or changing hyperlinks, correcting typos, and rephrasing for clarity).

March 6, 2025: Added.