Functions

Querona ships with a concrete set of supported functions, based on SQL Server’s functions, thus names and behavior are the same. In this reference, you will find detailed descriptions and examples of the built-in functions.

Performance requires functions to be executed close to the data. To satisfy this requirement, Querona pushes down a lot of data processing to the underlying engines, taking care of the incompatibilities between function’ implementations on different engines. For example, it may map the function to another, rewrite the function into an equivalent expression, or even execute the function on a separate engine while routing the necessary data.

Note

Because of the pushdowns, in rare, known scenarios, the same function may execute differently, depending on the engine that happens to be selected by the query optimizer.

Good examples of the differences are date and time functions on older versions of Spark. Apache Spark 2.x treats dates before 1582-10-15 as Julian calendar dates, and dates after 1582-10-15 as Gregorian calendar dates. SQL Server treats such dates differently, therefore date arithmetic calculations may yield different results.

Querona puts an effort to interpret and convert the dates returned by the engines, but in rare cases it cannot guarantee date arithmetic to be fully compatible with SQL Server in every scenario, due to different implementations of Julian/Gregorian dates handling.

Built-in functions are categorized as follows:

Aggregate

Function	Description
APPROX_COUNT_DISTINCT	Returns the number of rows in a data set that have distinct non-NULL values, where the return value is within a specified range of error tolerance. APPROX_COUNT_DISTINCT can return a value between 0 and 247.
AVG	Returns the average of the values in a group. Null values are ignored.
CHECKSUM_AGG	Returns the checksum of the values in a group. Null values are ignored. Can be followed by the OVER clause.
COUNT_BIG	Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.
COUNT	Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.
GROUPING	Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.
GROUPING_ID	Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified.
KURTOSIS	Returns the kurtosis of the values in the expression.
MAX	Returns the maximum value in the expression.
MIN	Returns the minimum value in the expression. May be followed by the OVER clause.
STDEV	Returns the statistical standard deviation of all values in the specified expression.
STDEVP	Returns the statistical standard deviation for the population for all values in the specified expression.
STRING_AGG	Concatenates the values of string expressions and places separator values between them.
SKEWNESS	Returns the skewness of the values in the expression.
SUM	Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
VAR	Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause.
VARP	Returns the statistical variance for the population for all values in the specified expression.

Analytical

Function	Description
CUME_DIST	Calculates the cumulative distribution of a value in a group of values. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row _r_, assuming ascending ordering, the CUME_DIST of _r_ is the number of rows with values lower than or equal to the value of _r_, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.
FIRST_VALUE	Returns the first value in an ordered set of values.
LAG	Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
LAST_VALUE	Returns the last value in an ordered set of values.
LEAD	Accesses data from a subsequent row in the same result set without the use of a self-join. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
PERCENTILE_CONT	Calculates a percentile based on a continuous distribution of the column value. The result is interpolated and might not be equal to any of the specific values in the column.
PERCENTILE_DISC	Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset. For a given percentile value _P_, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to _P_. For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values; the result is equal to a specific value in the column.
PERCENT_RANK	Calculates the relative rank of a row within a group of rows. Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function.

Azure

Function	Description
DETECT_FACES	Detects one or more human faces in an image and gets back face rectangles for where in the image the faces are, along with face attributes which contain machine learning-based predictions of facial features.
USER_TO_ITEM_RECOMMENDATION	Learns from previous transactions to predict which items are more likely to be of interest to or purchased by your customers.

Date and time

Function	Description
DATEADD	Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
DATEDIFF	Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
DATEFROMPARTS	Returns a date value for the specified year, month, and day.
DATENAME	Returns a character string that represents the specified datepart of the specified date.
DATEPART	Returns an integer that represents the specified datepart of the specified date.
DATETIME2FROMPARTS	Returns a datetime2 value for the specified date and time and with the specified precision.
DATETIMEFROMPARTS	Returns a datetime value for the specified date and time.
DATETIMEOFFSETFROMPARTS	NOT SUPPORTED.
DAY	Returns an integer representing the day (day of the month) of the specified date.
EOMONTH	Returns the last day of the month that contains the specified date, with an optional offset.
GETDATE	Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of Querona is running.
GETUTCDATE	Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of Querona is running.
ISDATE	Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ISDATE returns 0 if the expression is a datetime2 value.
MONTH	Returns an integer that represents the month of the specified date.
SMALLDATETIMEFROMPARTS	Returns a smalldatetime value for the specified date and time.
SWITCHOFFSET	NOT SUPPORTED.
SYSDATETIME	Returns a datetime2(7) value that contains the date and time of the computer on which the instance of Querona is running.
SYSDATETIMEOFFSET	NOT SUPPORTED.
SYSUTCDATETIME	Returns a datetime2 value that contains the date and time of the computer on which the instance of Querona is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.
TIMEFROMPARTS	Returns a time value for the specified time and with the specified precision.
TODATETIMEOFFSET	NOT SUPPORTED.
YEAR	Returns an integer that represents the year of the specified date.

Expression

Function	Description
COALESCE	Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
IIF	Returns one of two values, depending on whether the Boolean expression evaluates to true or false.
ISNULL	Replaces NULL with the specified replacement value.
NULLIF	Returns a null value if the two specified expressions are equal.

Json

Function	Description
ISJSON	Tests whether a string contains valid JSON.
JSON_QUERY	Extracts an object or an array from a JSON string.
JSON_VALUE	Extracts a scalar value from a JSON string.

Math

Function	Description
ABS	A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)
ACOS	A mathematical function that returns the angle, in radians, whose cosine is the specified float expression; also called arc cosine.
ASIN	Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.
ATAN	Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent.
ATN2	Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.
CEILING	Returns the smallest integer greater than, or equal to, the specified numeric expression.
COS	Is a mathematical function that returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
COT	A mathematical function that returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression.
DEGREES	Returns the corresponding angle in degrees for an angle specified in radians.
EXP	Returns the exponential value of the specified float expression.
FLOOR	Returns the largest integer less than or equal to the specified numeric expression.
LOG10	Returns the base-10 logarithm of the specified float expression.
LOG	Returns the natural logarithm of the specified float expression.
PI	Returns the constant value of PI.
POWER	Returns the value of the specified expression to the specified power.
RADIANS	Returns radians when a numeric expression, in degrees, is entered.
RAND	Returns a pseudo-random float value from 0 through 1, exclusive.
ROUND	Returns a numeric value, rounded to the specified length or precision.
SIGN	Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.
SIN	Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.
SQRT	Returns the square root of the specified float value.
SQUARE	Returns the square of the specified float value.
TAN	Returns the tangent of the input expression.

Metadata

Function	Description
QUA_PARTITION_HINT	Loads partitioning information. Should be used as a constraint of a table.

Pseudonymization

Function	Description
QUA_SHUFFLE	Allows data pseudonymization from an expression or from a column.
QUA_DESHUFFLE	Deanonymizes data by doing the opposite operation to QUA_SHUFFLE.
QUA_CONNECTIONSHUFFLEKEY	Gets the trusted connection salt from the connection.

Ranking

Function	Description
DENSE_RANK	Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
NTILE	Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
RANK	Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
ROW_NUMBER	Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

String

Function	Description
ASCII	Returns the ASCII code value of the leftmost character of a character expression.
CHAR	Converts an int ASCII code to a character.
CHARINDEX	Searches an expression for another expression and returns its starting position if found.
CONCAT	Returns a string that is the result of concatenating two or more string values.
FORMAT	Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings.
LEFT	Returns the left part of a character string with the specified number of characters.
LEN	Returns the number of characters of the specified string expression, excluding trailing blanks.
LEVENSHTEIN	Returns the Levenshtein distance between the two given strings.
LOWER	Returns a character expression after converting uppercase character data to lowercase.
LTRIM	Returns a character expression after it removes leading blanks.
NCHAR	Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
PATINDEX	Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
QUOTENAME	Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
REGEX_EXTRACT	Extracts a given group that matches the java-style regular expression.
REGEX_REPLACE	Replaces all occurrences of a specified string value that match a java-style regular expression with the replacement string.
REGEX_SPLIT	Extracts a group that matches the POSIX-style regular expression.
REPLACE	Replaces all occurrences of a specified string value with another string value.
REPLICATE	Repeats a string value a specified number of times.
REVERSE	Returns the reverse order of a string value.
RIGHT	Returns the right part of a character string with the specified number of characters.
RTRIM	Returns a character string after truncating all trailing spaces.
SPACE	Returns a string of repeated spaces.
SPLIT	Splits a string around a java-style regular expression.
STR	Returns character data converted from numeric data.
STUFF	The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
SUBSTRING	Returns part of a character, binary, text, or image expression.
UNICODE	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER	Returns a character expression with lowercase character data converted to uppercase.

System

Function	Description
@@DATEFIRST	Returns the current value, for a session, of SET DATEFIRST.
@@MAX_PRECISION	Returns the precision level used by decimal and numeric data types as currently set in Querona.
@@MICROSOFTVERSION	Returns version information of SQL Server which Querona is compatible with.
@@OPTIONS	Returns information about the current SET options.
@@SERVICENAME	Returns the name of the registry key under which Querona is running. @@SERVICENAME returns ‘SQL2012’.
@@SPID	Returns the session ID of the current user process.
@@TRANCOUNT	Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
@@VERSION	Returns system and build information for the current installation of Querona.
APP_NAME	Returns the application name for the current session if set by the application.
ASSEMBLYPROPERTY	Returns information about a property of an assembly.
COLLATIONNAME	Returns collation name by collation ID.
COLLATIONPROPERTY	Returns the property of a specified collation.
COLLATIONPROPERTYFROMID	Returns the property of a specified collation.
COL_LENGTH	Returns the defined length, in bytes, of a column.
COL_NAME	Returns the name of a column from a specified corresponding table identification number and column identification number.
COLUMNPROPERTY	Returns information about a column or parameter.
CURRENT_USER	Returns the name of the current user. This function is equivalent to USER_NAME.
DATABASE_PRINCIPAL_ID	Returns the ID number of a principal in the current database.
DATABASEPROPERTYEX	Returns the current setting of the specified database option or property for the specified database in Querona.
DB_ID	Returns the database identification (ID) number.
DB_NAME	Returns the database name.
FULLTEXTCATALOGPROPERTY	Returns information about full-text catalog properties.
FULLTEXTSERVICEPROPERTY	Returns information related to the properties of the Full-Text Engine.
HAS_DBACCESS	Returns information about whether the user has access to the specified database.
HASHBYTES	Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input.
HAS_PERMS_BY_NAME	Evaluates the effective permission of the current user on a securable.
INDEXPROPERTY	Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name. Returns NULL for XML indexes.
IS_MEMBER	Indicates whether the current user is a member of the specified Microsoft Windows group or Querona database role.
IS_ROLEMEMBER	Indicates whether a specified database principle is a member of the specified database role.
IS_SRVROLEMEMBER	Indicates whether a SQL Server login is a member of the specified server role.
JSON_PATH	Returns the content of the item queried by XPath expression.
LOAD_FILE	Loads file content in bytes.
NEWID	Creates a unique value of type uniqueidentifier.
OBJECT_ID	Returns the database object identification number of a schema-scoped object.
OBJECT_NAME	Returns the database object name for schema-scoped objects.
OBJECTPROPERTYEX	Returns information about schema-scoped objects in the current database. OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. |
OBJECTPROPERTY	Returns information about schema-scoped objects in the current database. OBJECTPROPERTY cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. |
OBJECT_SCHEMA_NAME	Returns the database schema name for schema-scoped objects.
PARSENAME	Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name.
QUA_GET_INCREMENTAL_MARKER	Returns a marker value for a last updated row in a table.
SCHEMA_ID	Returns the schema ID associated with a schema name.
SCHEMA_NAME	Returns the schema name associated with a schema ID.
SERVERPROPERTY	Returns property information about the server instance.
SESSIONPROPERTY	Returns the SET options settings of a session.
SUSER_SNAME	Returns the login identification name of the user.
SYSTEM_USER	Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.
TYPE_ID	Returns the ID for a specified data type name.
TYPE_NAME	Returns the unqualified type name of a specified type ID.
TYPEPROPERTY	Returns information about a data type.
USER_NAME	Returns a database user name from a specified identification number.

Tabular

Function	Description
fn_builtin_permissions	Returns a description of the all built-in permissions hierarchy of the server.
fn_helpcollations	Returns a list of all collations.
$GetColumnList	Not supported.
$GetTableList	Not supported.