Parameter Table Scan
Introduction
The Parameter Table Scan operator reads the internal rowset that’s produced during an INSERT … EXEC statement. This rowset contains the output of the executed code, which can be a stored procedure or a dynamic SQL batch.
Microsoft’s documentation used to claim that the Parameter Table Scan operator was “typically […] used for INSERT queries within a stored procedure”. I have never seen this operator used in this context, though. After I created a pull request, the documentation is now corrected. However, the incorrect information has over time been copied to multiple other websites, and will probably persist for many more years.
The only case where I have seen the Parameter Table Scan operator used is when executing an INSERT statement with the EXECUTE option, either to execute a stored procedure, or to execute a string with dynamic SQL, locally or at a remote server.
The execution plans related to the EXECUTE statement (that are only exposed for an execution plan with run-time statistics) do not return data to the client, but instead store their returned data in an (internal) “parameter table”. This is not visible in their execution plans.
When execution of the EXECUTE statement is finished, an extra execution plan reads this parameter table with the Parameter Table Scan operator, so that the data can be inserted in the target table.
If you find other used cases where a Parameter Table Scan is used, please let me know!
Visual appearance in execution plans
Depending on the tool being used, a Parameter Table Scan operator is displayed in a graphical execution plan as shown below:
|
SSMS and VS Code |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
 |
Algorithm
The basic algorithm for the Parameter Table Scan 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.
Read first / next row
The Parameter Table Scan operator does not have an Object property to specify which internal parameter to scan. That is because SQL Server supports just one single parameter table per connection (which is the reason why INSERT … EXECUTE statements cannot be nested).
The results of the EXECUTE statement are inserted into that single internal parameter table. (While you will see the execution plans for that EXECUTE statement when you request an execution plan with run-time statistics, these plans will not show that the results are being captured instead of being returned). The execution plan that uses Parameter Table Scan to read these stored results and insert them in the target table then instantly follows, reading from the just populated parameter table.
Operator properties
The properties below are specific to the Parameter Table Scan 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 Parameter Table Scan operator are marked with a *.
| Property name | Description |
|---|---|
| Estimated Number of Rows * | The Parameter Table Scan operator has a fixed cardinality estimate of just 1 row per execution. |
| Estimated Number of Rows for All Executions * | The Parameter Table Scan operator has a fixed cardinality estimate of just 1 row per execution. |
| Estimated Number of Rows Per Execution * | The Parameter Table Scan operator has a fixed cardinality estimate of just 1 row per execution. |
| Output List * | All columns in the Output List are always listed with both their Table and their Alias equal to “Parameter Table”. |
| TableCardinality * | This property is always 0 on a Parameter Table Scan. |
Implicit properties
This table below lists the behavior of the implicit properties for the Parameter Table Scan operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Parameter Table Scan operator supports row mode execution only. |
| Blocking | The Parameter Table Scan operator is non-blocking. |
| Memory requirement | The Parameter Table Scan operator does not have any special memory requirement. |
| Order-preserving | Technically speaking, the Parameter Table Scan operator should be considered order-preserving. However, the optimizer does not have any information about the order of rows returned by the EXECUTE statement, and hence the output of a Parameter Table Scan is considered unordered. |
| Parallelism aware | It is currently unknown whether the Parameter Table Scan operator can be used in parallel execution plans. |
| Segment aware | The Parameter Table Scan 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 1, 2026: Added.