Functions

Querona ships with a concrete set of supported functions. They are based on SQL Server’s functions thus a name and behavior are the same in most cases. Using this reference you can find all detailed descriptions and examples about 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. Because of the pushdowns, the same function may execute slightly differently, depending on the engine that happens to be selected by the query optimizer. Good examples of the mentioned differences are date and time functions. Apache Spark 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 the dates returned by engines to be compatible with SQL Server, but it can not guarantee date arithmetic to be fully compatible due to different implementations of Julian/Gregorian dates handling.

Functions in Querona are categorized like the following:

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.

Datetime

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.

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.