Table Valued Function
Introduction
The Table Valued Function operator executes a table-valued function, which can be either a user-defined multi-statement table-valued function, or a built-in table valued function. The results of the executed function are always stored in an internal table variable, that can be read by a Table Scan, Index Scan, or Clustered Index Scan operator elsewhere in the execution plan. The Table Valued Operator itself can optionally also return the rows that the executed table-valued function created.
Visual appearance in execution plans
Depending on the tool being used, a Table Valued Function 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 Table Valued Function 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. Some of the details of how heaps and indexes are read are also omitted for readability.
Rebind / rewind
A Table Valued Function operator on the inner input of a Nested Loops operator will avoid needless overhead by not invoking the logic of the table-valued function when the execution is a rewind. The effect is that the contents of the internal table variable holding the execution result are unchanged, so they will be returned again without additional work.
For a rebind, which includes the first execution as well as any execution not on the inner input of a Nested Loops, the table-valued function will always be executed, replacing the previous execution results (if any) in the internal table variable with the results of the new execution.
Execute function
If the Object property lists the name of a user-defined multi-statement table-valued function (UMTF), then the execution of the current execution plan pauses. SQL Server will check if there are already execution plans for the UMTF in the plan cache; if not then it will now invoke the optimizer to create the execution plans for each statement in the code. After that, the generated execution plans are executed. Execution of the Table Valued Function operator resumes once the UMTF has finished execution.
If the Object property lists a built-in internal table-valued function (BITF), then the execution of the current execution plan pauses. SQL Server invokes the internal logic of the BITF. Execution of the Table Valued Function operator resumes once the BITF has finished execution.
Return data?
The rows that are generated by executing the UDTF or BITF are stored in an internal table variable. This internal table variable can be read by a normal Table Scan, Index Scan, or Clustered Index Scan operator, to inject these rows anywhere they are needed in the execution plan. Optionally, the Table Valued Function operator can also return the rows from the internal table variable immediately after executing the UDTF or BITF.
There is no explicit property that specifies whether a Table Valued Function operator returns rows or not, but it is implied by the presence of an Output List property that matches the list of columns that the UDTF or BITF returns, plus a Defined Values property that lists those same columns, without specification.
Read first row / Read next row
The logic to return rows from the internal table variable is exactly the same as the logic for returning those same rows by a normal Table Scan, Index Scan, or Clustered Index Scan operator, except that the Table Valued Function operator cannot have a Predicate property to filter rows as they are read and returned.
Please see the descriptions of Table Scan, Index Scan, and Clustered Index Scan for details of how rows in these structures are accessed.
Built-in internal table-valued functions
As mentioned in the text above, the Table Valued Function operator is not only used to execute user-defined functions, but also for internal functions that SQL Server uses to execute specific logic. These functions are not documented, but based on observation of execution plans, the below is a list of currently known internal table-valued functions.
Fulltextmatch
A Table Valued Function operator with the Object property set to “Fulltextmatch” is used to evaluate full-text search expressions against a full-text index. The exact full-text search specification to be evaluated by the internal table-valued function is not exposed in the graphical execution plan, nor in the execution plan XML.
The Fulltextmatch internal table-valued function accepts five parameters. In all cases I have seen so far, the same hardcoded values are passed: 0, 1, 0, NULL, and NULL.
The Fulltextmatch internal table-valued function returns a table of up to two columns, called docid (the value in the key index of a document that matches the full-text search specification) and score (the confidence score of the match).
GENERATE_SERIES
A Table Valued Function operator with the Object property set to “GENERATE_SERIES” generates a series of numbers within a given interval. If it functionaly equivalent to the T-SQL GENERATE_SERIES keyword.
The GENERATE_SERIES internal table-valued function accepts two or three parameters. The first and second parameter are the first and last value in the interval. The optional third parameter is the step size. When not given, the step size defaults to 1 if stop > start, or to -1 otherwise.
The GENERATE_SERIES internal table-valued function returns a table with one columns, called value.
The GENERATE_SERIES internal table-valued function was introduced in SQL Server 2022.
OPENJSON_DEFAULT
A Table Valued Function operator with the Object property set to “OPENJSON_DEFAULT” performs a default extraction of relational data from an nvarchar(max) input parameter that contains JSON data. This is equivalent to using OPENJSON without a WITH clause.
The OPENJSON_ DEFAULT internal table-valued function accepts two parameters. The first parameter is the nvarchar(max) value that stores the JSON to be parsed, and the second parameter is used for the optional path.
The OPENJSON_ DEFAULT internal table-valued function returns a table with three columns, called key, value, and type.
OPENJSON_EXPLICIT
A Table Valued Function operator with the Object property set to “OPENJSON_EXPLICIT” extracts specified columns from an nvarchar(max) input parameter that contains JSON data. This is equivalent to using OPENJSON with the WITH clause.
The OPENJSON_EXPLICIT internal table-valued function requires three or more parameters. The first parameter is the nvarchar(max) value that stores the JSON to be parsed, and the second parameter is used for the optional path. The third and later parameters specify the columns to be extracted, specified as “$.”, followed by the name of that column.
The OPENJSON_EXPLICIT internal table-valued function returns a table with the columns as specified in the WITH clause.
PlanarTesselation
A Table Valued Function operator with the Object property set to “PlanarTesselation” is used to compute the tessellation of a spatial object. The result is usually joined to a spatial index to quickly find rows that might match a spatial function on the given object. Due to the imprecision of the tessellation process, not all matches found this way are certain.
The PlanarTesselation internal table-valued function accepts nine parameters. The first parameter is the spatial object that will be tesselated. The other eight parameters are probably various settings that determine the exact rules to apply for the tessellation, such as whether to use Geometry or Geography rules, the bounding box, etc.
The PlanarTesselation internal table-valued function returns a table of three columns, called Id (the number of the first cell number in a range of consecutive cells with the same Attr value), Attr (an indicator to distinguish cells that are partially covered by the object (Attr = 1) from cells that are completely covered by it (Attr = 2)), and LimitId (the number of the last cell number in a range of consecutive cells with the same Attr value).
XML Reader
A Table Valued Function operator with the Object property set to “[XML Reader]” is used to collect the individual elements of a node to return from an XPath or XQuery expression against an XML column after the selection of nodes to return has been done by using one or more Table Valued Function operators with the Object property set to “[XML Reader with XPath filter]” (see below).
The [XML Reader] internal table-valued function accepts four parameters. The first parameter is always given, and is a reference to the representation of a single node in an internal node table of an XML fragment. The second parameter is an integer value, probably a collection of flags that determines the exact behavior of the [XML Reader]. The third and fourth parameters may be optional; both represent a single node in an internal node table of an XML fragment.
The [XML Reader] internal table-valued function returns a table of eleven columns, called id, nid, taguri, tagname, tid, value, lvalue, lvaluebin, hid, xsinil, and xsitype. These are the columns that are stored in any internal node table of an XML fragment. The returned values are typically passed to an UDX operator that extracts the actual values from these internal columns.
XML Reader with XPath filter
A Table Valued Function operator with the Object property set to “[XML Reader with XPath filter]” is used to evaluate part or all of an XPath or XQuery expression against an XML column. More complex expressions tend to be evaluated in multiple steps, where each next step builds on the output of the previous step. The final step is in most cases the execution of a Table Valued Function operator with the Object property set to “[XML Reader]” (see above).
The exact XPath or XQuery specification to be evaluated by the internal table-valued function is not exposed in the graphical execution plan, nor in the execution plan XML.
The [XML Reader with XPath filter] internal table-valued function accepts four parameters. The first parameter is always given, and is a reference to the representation of a single node in an internal node table of an XML fragment. The second parameter is an integer value, probably a collection of flags that determines the exact behavior of the [XML Reader with XPath filter]. The third and fourth parameters are both optional, and both represent a single node in an internal node table of an XML fragment.
The [XML Reader with XPath filter] internal table-valued function returns a table of just one column, called id. The values in this column represent individual nodes in an internal node table of an XML fragment.
Operator properties
The properties below are specific to the Table Valued Function 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 Table Valued Function operator are marked with a *.
| Property name | Description |
|---|---|
| Defined Values * | If this property is present but empty, then the Table Valued Function operator only executes the table-valued function, but does not return its results. If this property is present and non-empty, then it specifies the columns for the rows to be returned after evaluating the table-valued function. |
| IsInterleavedExecuted | This property is present and set to True if interleaved execution was applied when the plan was executed. This means that execution of the execution plan was interrupted after execution of the Table Valued Function operator; the actual cardinality of the internal table variable with the results of the function was sniffed, and the rest of the execution plan was recompiled based on this actual cardinality rather than the hardcoded estimate of 100 rows that would normally be used. When an execution plan is reused from the plan cache, the Table Valued Function will sometimes (but not always) display the IsInterleavedExecuted property, with its value set to True. This is a bug, interleaved execution is never applied to a cached plan. Only in execution plans with run-time statistics. Introduced in SQL Server 2017. |
| Object | This property lists the name of the table-valued function to be executed. This can be either a fully qualified object name, to execute a user-defined function; or the name of an internal table-valued function. See the main text for name and description of known internal table-valued functions. |
| Output List * | If this property is present but empty, then the Table Valued Function operator only executes the table-valued function, but does not return its results. If this property is present and non-empty, then it specifies the columns for the rows to be returned after evaluating the table-valued function. |
| Parameter List | This property lists the parameters to be passed into the table-valued function and their values. |
Implicit properties
This table below lists the behavior of the implicit properties for the Table Valued Function operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Table Valued Function operator supports row mode execution only. |
| Blocking | The Table Valued Function operator is fully blocking. |
| Memory requirement | The Table Valued Function operator does not have any special memory requirement when it executes a user-defined multi-statement table-valued function. The Table Valued Function operator does appear to require extra memory when it executes one of the known internal table-valued functions. No details about the amount of memory needed are known at this time. It is also unknown whether these operators can spill to tempdb if at run-time they require more memory than is available. |
| Order-preserving | If the Table Valued Function operator returns rows, then they are returned in the order in which they are stored in the heap, or in clustered index order if the table variable that holds the generated data has a clustered index. For purposes of plan optimization, the optimizer always considers the output of a Table Valued Function operator to be unordered. However, if the same output is read by a separate Index Scan or Clustered Index Scan operator, then the output will be considered ordered if the Ordered property of that operator is set to True. |
| Parallelism aware | The Table Valued Function operator is not parallelism aware. |
| Segment aware | The Table Valued Function operator is not segment aware. |