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 (current versions)	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. It is functionally 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 column, called value.

The GENERATE_SERIES internal table-valued function was introduced in SQL Server 2022.

INDEXANALYSIS

A Table Valued Function operator with the Object property set to “INDEXANALYSIS” returns size and fragmentation information for one or more indexes. It is used to return data for the dynamic management object sys.dm_db_index_physical_stats.

The INDEXANALYSIS internal table-valued function accepts five parameters: the database_id, object_id, index_id, partition_number, and mode. These work the same as the corresponding parameters for sys.dm_db_index_physical_stats.

The INDEXANALYSIS internal table-valued function returns a table with (as of SQL Server 2019) up to 30 columns. These correspond to the columns returned by sys.dm_db_index_physical_stats.

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).

QUERY_STORE_PLAN_FEEDBACK_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_PLAN_FEEDBACK_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_PLAN_FEEDBACK_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_PLAN_FEEDBACK_IN_MEM internal table-valued function returns a table of up to seven columns. These have the same name and same content as the columns in the sys.query_store_plan_feedback catalog view. The Output List property specifies (by name) which of these seven columns are returned.

The QUERY_STORE_PLAN_FEEDBACK_IN_MEM internal table-valued function was introduced in SQL Server 2022.

QUERY_STORE_PLAN_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_PLAN_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_PLAN_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_PLAN_IN_MEM internal table-valued function returns a table of up to twenty columns. Nineteen of these have the same name and same content as the columns in the sys.query_store_plan catalog view. The last column, plan_flags, is the combined binary encoding of the remaining columns from this same catalog view. The Output List property specifies (by name) which of these twenty columns are returned.

The QUERY_STORE_PLAN_IN_MEM internal table-valued function was introduced in SQL Server 2016.

QUERY_STORE_QUERY_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_QUERY_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_QUERY_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_QUERY_IN_MEM internal table-valued function returns a table of up to twenty-nine columns. Twenty-eight of these have the same name and same content as the columns in the sys.query_store_query catalog view. The last column, plan_flags, is the combined binary encoding of the remaining columns from this same catalog view. The Output List property specifies (by name) which of these twenty-nine columns are returned.

The QUERY_STORE_QUERY_IN_MEM internal table-valued function was introduced in SQL Server 2016.

QUERY_STORE_QUERY_TEXT_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_QUERY_TEXT_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_QUERY_TEXT_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_QUERY_TEXT_IN_MEM internal table-valued function returns a table of up to five columns. These have the same name and same content as the columns in the sys.query_store_query_text catalog view. The Output List property specifies (by name) which of these five columns are returned.

The QUERY_STORE_QUERY_TEXT_IN_MEM internal table-valued function was introduced in SQL Server 2016.

QUERY_STORE_QUERY_VARIANT_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_QUERY_VARIANT_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_QUERY_VARIANT_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_QUERY_VARIANT_IN_MEM internal table-valued function returns a table of up to three columns. These have the same name and same content as the columns in the sys.query_store_query_variant catalog view. The Output List property specifies (by name) which of these three columns are returned.

The QUERY_STORE_QUERY_VARIANT_IN_MEM internal table-valued function was introduced in SQL Server 2022.

QUERY_STORE_RUNTIME_STATS_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_RUNTIME_STATS_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_RUNTIME_STATS_IN_MEM internal table-valued function accepts eight or ten parameters. The fourth parameter appears to be a plan_id, or NULL to retrieve information for all plans. The function of the other parameters is currently unknown.

The QUERY_STORE_RUNTIME_STATS_IN_MEM internal table-valued function returns a table of up to seventy-three columns. These have the same name and same content as the columns in the sys.query_store_runtime_stats catalog view. The Output List property specifies (by name) which of these seventy-three columns are returned.

The QUERY_STORE_RUNTIME_STATS_IN_MEM internal table-valued function was introduced in SQL Server 2016.

QUERY_STORE_RUNTIME_STATS_INTERVAL_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_RUNTIME_STATS_INTERVAL_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_RUNTIME_STATS_INTERVAL_IN_MEM internal table-valued function does not take any parameters.

The QUERY_STORE_RUNTIME_STATS_INTERVAL_IN_MEM internal table-valued function returns a table of up to four columns. These have the same name and same content as the columns in the sys.query_store_runtime_stats_interval catalog view. The Output List property specifies (by name) which of these four columns are returned.

The QUERY_STORE_RUNTIME_STATS_INTERVAL_IN_MEM internal table-valued function was introduced in SQL Server 2016.

QUERY_STORE_WAIT_STATS_IN_MEM

A Table Valued Function operator with the Object property set to “QUERY_STORE_WAIT_STATS_IN_MEM” returns Query Store data from memory, so data that has been collected but not yet written to the permanent tables of the Query Store.

The QUERY_STORE_WAIT_STATS_IN_MEM internal table-valued function accepts ten or twelve parameters. The function of these parameters is currently unknown.

The QUERY_STORE_WAIT_STATS_IN_MEM internal table-valued function returns a table of up to twelve columns. These have the same name and same content as the columns in the sys.query_store_wait_stats catalog view. The Output List property specifies (by name) which of these twelve columns are returned.

The QUERY_STORE_WAIT_STATS_IN_MEM internal table-valued function was introduced in SQL Server 2017.

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 a 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 up to 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. They are not always all returned; it is in fact not uncommon to return only the id column, which represents individual nodes in an internal node table of an XML fragment; or any other subset of these eleven columns.

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.