Scalar Functions

Introduction

Many operators can use scalar expressions in their properties. The best example of this is the Defined Values property of a Compute Scalar operator, but there are other properties and other operators where you can find expressions as well.

Those scalar expressions can use simple operands such as + or *, but in many cases they can also call various functions. Most of those functions are simply the same scalar functions that T-SQL supports; these are all described in Books Online.

Internal scalar functions

But the optimizer also has a set of scalar functions available that are not allowed within T-SQL code, but that it can use in properties of some operators. The table below lists all these functions, with a description of their input parameters and their result.

Function nameParametersDescription
APPROX_COUNT_DISTINCT_CONVERT (expression)expressionDecodes expression, which encodes multiple values into a single column, into its parts; then applies the “Count” logic of the hyperloglog algorithm.
Note that expression is the output of the APPROX_COUNT_DISTINCT_ACCUM aggregate function.
CONVERT_IMPLICITdata_type
expression
third
Converts expression from its current data type to data_type.
The third parameter is an integer value; its function is currently unknown; I suspect that it is similar to the style parameter of the CONVERT function.
ConvertSVToXmlcolumn_set
second
Converts column_set from the internal storage format used for sparse columns to XML.
The second parameter is an integer value; its function is currently unknown.
ConvertXmlToRESVxml
second
Converts xml from XML to the internal storage format used for sparse columns.
The second parameter is an integer value; its function is currently unknown.
ForceOrderexpressionUnknown; probably used to ensure that expression is evaluated without reordering elements for performance gain.
getancestornode_id
second
Used in XQuery evaluation to find the ancestor node of a node in an XML fragment.
The node_id parameter represents a node in the internal node table of an XML fragment. The value returned is the node_id of the ancestor of node_id.
The second parameter is an integer value; its function is currently unknown; I suspect that it is used to specify the number of levels to go up in the hierarchy of the XML fragment.
getdescendantlimitnode_idUsed for XQuery evaluation to find the highest numbered descendant node of a node in an XML fragment.
The node_id parameter represents a node in the internal node table of an XML fragment. The value returned is the highest numbered node_id that is a descendant of node_id.
GetRangeThroughConvert
(not properly displayed in Management Studio)
begin_expression
end_expression
seek_flags
This function is not properly displayed in Management Studio. When the Defined Values property includes a GetRangeThroughConvert function, it shows as empty.
This function converts a range specified in one data type to a range in another data type; this is typically done to allow the use of an Index Seek operator when the data types of the indexed column and the value to find do not match.
The seek_flags parameter is a set of flags, encoded in an integer value, to fine-tune the exact behavior of the seek operator. See Dynamic seek range for more information.
GetRangeThroughConvert returns a vector, a set of three values. Two of these are the lower and upper bound of the range in the target data type; the third appears to be always equal to the seek_flags parameter.
GetRangeWithMismatchedTypes
(not properly displayed in Management Studio)
begin_expression
end_expression
seek_flags
This function is not properly displayed in Management Studio. When the Defined Values property includes a GetRangeWithMismatchedTypes function, it shows as empty.
This function converts a range specified in one data type to a range in another data type; this is typically done to allow the use of an Index Seek operator when the data types of the indexed column and the value to find do not match.
The seek_flags parameter is a set of flags, encoded in an integer value, to fine-tune the exact behavior of the seek operator. See Dynamic seek range for more information.
GetRangeWithMismatchedTypes returns a vector, a set of three values. Two of these are the lower and upper bound of the range in the target data type; the third appears to be always equal to the seek_flags parameter.
GetSparseColumnValuecolumn_set
column_no
Extracts the value of a single sparse column from column_set, which is stored in the internal storage used for sparse columns.
The column_no parameter is the column_id of he column to extract (as stored in sys.columns).
LikeRangeEndexpressionReturns the high end of a range that encloses all values that can match a LIKE expression predicate, so that matching rows can be found using an Index Seek or other seek operator.
LikeRangeInfoexpressionReturns a set of flags, encoded in an integer value, to define certain seek properties for an Index Seek or other seek operator to find all rows in a range defined by a LIKE expression predicate. See Dynamic seek range for more information.
LikeRangeStartexpressionReturns the low end of a range that encloses all values that can match a LIKE expression predicate, so that matching rows can be found using an Index Seek or other seek operator.
manufactureconstructordpathfirst
node_id
third
Used in XQuery evaluation; it is currently not known what exactly this function does, or what it returns.
The first parameter is a binary value; its function is currently unknown. I suspect that it holds binary encoded flags to control the behavior of the function.
The node_id parameter represents a node in the internal node table of an XML fragment.
The third parameter is an integer value; its function is currently unknown.
manufacturesubnodeordpathnode_id_1
node_id_2
node_id_3
Used in XQuery evaluation; it is currently not known what exactly this function does; the data is returns is typically passed to a UDX operator.
The three parameters node_id_1, node_id_2, and node_id_3 each represent a node in the internal node table of an XML fragment.
RaiseIfNullInsertexpressionThis function is used in insert operators to force an error is a NULL is inserted in a column that doesn’t allow NULL.
If expression is NULL, the function raises a run-time error that aborts execution and rolls back the transaction. If expression is not NULL, then the function returns expression, unchanged and in its own data type.
RaiseIfNullUpdateexpressionThis function is used in update operators to force an error is a column that doesn’t allow NULL would be updated to the NULL value.
If expression is NULL, the function raises a run-time error that aborts execution and rolls back the transaction. If expression is not NULL, then the function returns expression, unchanged and in its own data type.
sysmaxdatetimelenThe highest supported datetime2 value, based on len decimal places for the seconds part. The data type is always datetime2(7).
So sysmaxdatetime(3) would be 9999-12-31 23:59:59.9990000.
systrandatetimeThe date and time the current transaction (which may be an implicit transaction) started, in UTC, returned as datetime2(7).

Not properly displayed

In the table above, a few functions are marked as “(not properly displayed in Management Studio)”. These functions have (so far) only been seen in the Defined Values property of a Compute Scalar operator. When this happens, the function can be seen from the actual XML of the execution plan, but Management Studio is unable to properly display it. In most cases, it responds by simply ignoring the function and pretending it doesn’t exist.

Other tools may have their own limitations on which functions they can and cannot display.

Missing any function?

There is no official documentation (that I know of) of these internal scalar functions. The list above has been compiled based on what I have observed in execution plans that I have seen. It is very likely that this list is not complete yet.

You can help me complete this list! If you encounter an execution plan where Compute Scalar uses a keyword not listed here, please save it as a .sqlplan file and send it to me.

Thanks!

Menu

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close