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.