Put for SQL Server 2022 plus
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 new version of the Put operator, that is used in SQL Server 2022 and later.
In SQL Server 2022 and later, writing to Azure Storage or S3-compatible storage is only possible by using the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement. As far as I know, there are no other statements in SQL Server 2022 and later that can generate an execution plan with the Put operator.
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.
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 2022 and later is as shown below:
Note that this flowchart is a simplification.
Read batch
On SQL Server 2022 and later, the Put operator always runs in batch mode. That is reflected in the flowchart above, that shows that data is read from the child operator one batch at a time. See below for more information about the execution mode.
Send batch
On SQL Server 2022 and later, the Put operator sends each batch of rows it receives to the Azure service, for storing in the configured container.
Execution mode
The following considerations apply for row mode versus batch mode execution, and for using the Put operator in parallel execution plans.
Row mode versus batch mode
On SQL Server 2022 and later, the Put operator can only run in batch mode. Even when the query does not meet the criteria for batch mode, the Put operator itself still runs in batch mode. In this case, Put’s child operator reports running in row mode, but still reports the Actual Number of Batches property, which suggests that the conversion to batch mode is done within the operator. Also, if the child operator is a Compute Scalar, then deferred evaluation can’t be used.
If a CREATE EXTERNAL TABLE AS SELECT statement uses the DISALLOW_BATCH_MODE query hint, then that hint is respected for the rest of the execution plan, but not for the Put operator. This operator will still run in batch mode.
Parallelism
On SQL Server 2022 and later, when the Put operator runs in a parallel section of the execution plan, then each thread will create its own file in the Azure Storage or S3-compatible storage container. So if the plan runs on four threads, you will get four files.
When querying an external table that consists of multiple files, the Remote Scan operator can run in parallel. If the external table is stored in one single file, Remote Scan will always run serially.
Operator properties
The properties below are specific to the Put operator on SQL Server 2022 and later, 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 2022 and later are marked with a *.
| Property name | Description |
|---|---|
| Actual Number of Batches * | This is always 0 for the Put operator on SQL Server 2022 and later, because it does not return any rows to its parent. |
| Actual Number of Rows * | This is always 0 for the Put operator on SQL Server 2022 and later, because it does not return any rows to its parent. |
| Estimated Number of Rows * | This is always 1 for the Put operator on SQL Server 2022 and later, because it does not return any rows to its parent. |
| Output List * | Even though the Put operator on SQL Server 2022 and later does not return any rows to its parent, it still includes this property, with the same list of columns that Put receives from its child operator. |
Implicit properties
This table below lists the behavior of the implicit properties for the Put operator on SQL Server 2022 and later.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Put operator on SQL Server 2022 and later supports batch mode execution only. See the main text for more information. |
| Blocking | Since the Put operator on SQL Server 2022 and later does not return any rows, the distinction between blocking or non-blocking does not apply. |
| Memory requirement | The Put operator on SQL Server 2022 and later does not have any special memory requirement. |
| Order-preserving | Since the Put operator on SQL Server 2022 and later does not return any rows, there is no order to its output. |
| Parallelism aware | The Put operator on SQL Server 2022 and later is not parallelism aware. |
| Segment aware | The Put operator on SQL Server 2022 and later 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.