Transact-SQL functions can be either aggregate functions or scalar functions. The following sections describe these function types.
1. Aggregate Functions
Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value. Transact-SQL supports several groups of aggregate functions:
- Convenient aggregate functions
- Statistical aggregate functions
- User-defined aggregate functions
- Analytic aggregate functions
Statistical and analytic aggregate functions are discussed in Chapter 24. User-defined aggregates are beyond the scope of this book. That leaves the convenient aggregate functions, described next:
- AVG Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
- MAX and MIN Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values. The maximum (minimum) value of a string depends on the collation that your system uses.
- SUM Calculates the total of all data values in a column. The column must contain numeric values.
- COUNT Calculates the number of (non-NULL) data values in a column. The only aggregate function that is not applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
- COUNT_BIG Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.
The use of convenient aggregate functions with the SELECT statement can be found in Chapter 6.
2. Scalar Functions
In addition to aggregate functions, Transact-SQL provides several scalar functions that are used in the construction of scalar expressions. (A scalar function operates on a single value or list of values, whereas an aggregate function operates on the data from multiple rows.) Scalar functions can be categorized as follows:
- Numeric functions
- Date functions
- String functions
- System functions
- Metadata functions
The following sections describe these function types.
2.1. Numeric Functions
Numeric functions within Transact-SQL are mathematical functions for modifying numeric values. The following numeric functions are available:
2.2. Date Functions
Date functions calculate the respective date or time portion of an expression or return the value from a time interval. Transact-SQL supports the following date functions:
2.3. String Functions
String functions are used to manipulate data values in a column, usually of a character data type. Transact-SQL supports the following string functions:
2.4. System Functions
System functions of Transact-SQL provide extensive information about database objects. Most system functions use an internal numeric identifier (ID), which is assigned to each database object by the system at its creation. Using this identifier, the system can uniquely identify each database object. System functions provide information about the Database Engine. The following table describes several system functions. (For the complete list of all system functions, please see Microsoft Docs.)
All functions can be nested in any order; for example, REVERSE(CURRENT_USER).
2.5. Metadata Functions
Generally, metadata functions return information about the specified database and database objects. The following table describes several metadata functions. (For the complete list of all metadata functions, please see Microsoft Docs.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.