Put for SQL Server 2016 to 2019
Introduction
The Put operator is used to insert data in external (Polybase) tables. This operator was introduced in SQL Server 2016. At that time, the only writeable Polybase target was Hadoop. That changed in SQL Server 2022, when support for Hadoop was removed from the product, but Azure Storage and S3 compatible storage were introduced as new writable Polybase targets. The Put operator is used in both cases. However, its properties and behavior have completely changed as of SQL Server 2022, which leads me to believe that the code was fully replaced, to be a new operator that reuses the same name.
This page describes the original version of the Put operator, that is used in SQL Server 2016 to 2019.
Unlike regular insert operators (Clustered Index Insert, Columnstore Index Insert, Index Insert, Remote Insert, and Table Insert), but similar to Online Index Insert, both versions of the Put operator do not return any rows to their parent operator.
Request: I do not have access to any Hadoop cluster, so I was not able to run any tests to find out more details of the Put operator when it targets Hadoop. My only source at the time of writing was one single, and very simple, execution plan that someone sent me a few years ago. If you have access to a Hadoop cluster and a SQL Server 2016, 2017, or 2019 instance, and you are willing and allowed to use these for testing, then please contact me here.
Visual appearance in execution plans
Depending on the tool being used, a Put operator is displayed in a graphical execution plan as shown below, for all SQL Server versions:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
(not supported) |
 |
(no icon) |
Algorithm
The basic algorithm for the Put operator in SQL Server 2016 to 2019 is as shown below:
Note that this flowchart is a simplification.
Send query
In SQL Server 2016 to 2019, all work to find the data to insert into the external table on Hadoop is offloaded to the Hadoop cluster itself. The only thing that the Put operator does is to send the HiveQL query to execute to the cluster. This query is stored in the Remote Query property of the Put operator, in the form of an XML representation of the Hive Explain Plan.
Operator properties
The properties below are specific to the Put operator on SQL Server 2016 to 2019, 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 Put operator on SQL Server 2016 to 2019 are marked with a *.
| Property name | Description |
|---|---|
| Actual Number of Rows * | This is always 0 for the Put operator on SQL Server 2016 to 2019, because it does not return any rows to its parent. |
| Estimated Number of Rows * | This is always equal to the estimated number of rows that will be inserted by the Remote Query, even though in reality, no rows are returned to the parent operator. |
| Output List * | Since no data is returned to the parent operator, this property is always empty. |
| Remote Destination | In the single execution plan I have seen for a Put operator on SQL Server 2016 to 2019, the Remote Destination was set to “Polybase_ExternalComputation”. I do not know if that is an encoded value with a special meaning to the operator, or the name of the external data source on the server where that execution plan was created. See my request at the top of the page for how you can help me test this operator. |
| Remote Query | This query stores the HiveQL query that must be executed on the Hadoop cluster. It is stored as the XML representation of the Hive Explain Plan. |
Implicit properties
This table below lists the behavior of the implicit properties for the Put operator on SQL Server 2016 to 2019.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Put operator on SQL Server 2016 to 2019 supports row mode execution only. |
| Blocking | Since the Put operator on SQL Server 2016 to 2019 does not return any rows, the distinction between blocking or non-blocking does not apply. |
| Memory requirement | The Put operator on SQL Server 2016 to 2019 does not have any special memory requirement. |
| Order-preserving | Since the Put operator on SQL Server 2016 to 2019 does not return any rows, there is no order to its output. |
| Parallelism aware | The Put operator on SQL Server 2016 to 2019 is not parallelism aware. |
| Segment aware | The Put operator on SQL Server 2016 to 2019 is not segment aware. |
Change log
(Does not include minor changes, such as adding, removing, or changing hyperlinks, correcting typos, and rephrasing for clarity).
August 4, 2025: Added.