Remote Delete
Introduction
The Remote Delete operator deletes rows from 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 deletion. If an error is returned from the remote server, then the transaction is aborted and the same error is raised locally.
Cases where Remote Delete are used are, based on my observations so far, are:
- Using a DELETE statement with an OPENQUERY expression as the target.
- Using a DELETE statement with an OPENROWSET expression as the target, if the remote object is specified as a query.
In these cases, a Remote Scan executes the query on the remote server, and after optional processing on the local side, the rows to be deleted are sent using Remote Delete.
SQL Server also supports several other scenarios to delete data from remote objects, but in my tests, these use a Remote Query to send the entire delete statement to the remote server, rather than fetching rows and then using Remote Delete to delete rows:
- Using a DELETE statement with a remote table or view as the target.
- Using a DELETE statement with an OPENDATASOURCE expression as the target.
- Using a DELETE statement with an OPENROWSET expression as the target, if the remote object is specified by object name.
If you find any cases where the Remote Delete 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 Delete operator is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
 |
Algorithm
The basic algorithm for the Remote Delete 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.
Delete row
The Delete row action sends the key value(s) of the row to be deleted to the Remote Object, in the form of a single “bookmark” column. This 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 Delete if the rows needs to be deleted.
For a delete from 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 a delete from 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 a delete from 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 a delete from 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 Delete I have seen so far have only this single column in the input. So either the Remote Delete operator requires that the input can only be the “bookmark” column, 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 Delete 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 Delete operator are marked with a *.
| Property name | Description |
|---|---|
| Output List * | In all cases so far where I have looked at the Remote Delete operator, the Output List property was empty in the XML and not shown 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. |
| Remote Object | The object (table or view name) on the remote server that needs to be deleted from, using three-part naming (database name, schema name, and table or view name). When Remote Delete 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 deleted from the Remote Object. When Remote Delete is used to delete from an OPENDATASOURCE or OPENROWSET expression, this property shows “ |
Implicit properties
This table below lists the behavior of the implicit properties for the Remote Delete operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Remote Delete operator supports row mode execution only. |
| Blocking | The Remote Delete operator is non-blocking. |
| Memory requirement | The Remote Delete operator does not have any special memory requirement. |
| Order-preserving | I assume that the Remote Delete operator is fully order-preserving. However, since I have not been able to construct execution plans where the Remote Delete operator returns actual data to its parent, so I have not yet been able to verify this assumption. |
| Parallelism aware | The Remote Delete operator can only be used in serial execution plans. |
| Segment aware | The Remote Delete 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).
February 24, 2025: Added.
March 6, 2025: Fixed various small mistakes and oversights and improved consistency in various descriptions.