SQL Functions in Relational Databases

Functions are database objects that contain programming statements to perform a specific task, such as complex calculations, data manipulation, or encapsulating business logic. In SQL Server, functions can return a single value (scalar functions) or a table (table-valued functions).

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause.

• AVG(expression): Returns the average value of a column.
• COUNT(expression): Returns the number of rows in a table.
• MAX(expression): Returns the largest value in a column.
• MIN(expression): Returns the smallest value in a column.
• SUM(expression): Returns the total sum of a numeric column.

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Scalar Functions

Scalar functions return a single data value. They can be used in queries, stored procedures, and other database objects.

• UCASE(string) / UPPER(string): Converts a string to uppercase.
• LCASE(string) / LOWER(string): Converts a string to lowercase.
• ROUND(number, decimals): Rounds a number to a specified number of decimal places.
• GETDATE() / CURRENT_TIMESTAMP: Returns the current database system date and time.
• LEN(string) / DATALENGTH(string): Returns the length of a string or data.

SELECT GETDATE();

Table-Valued Functions

Table-valued functions (TVFs) return a table. They can be used in the FROM clause of a query, similar to a view, but they can also accept parameters.

• Inline Table-Valued Functions (ITVF): Contain a single SELECT statement.
• Multi-Statement Table-Valued Functions (MSTVF): Contain multiple T-SQL statements to build the result table.

CREATE FUNCTION dbo.GetEmployeesHiredAfter (@hireDate DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM Employees
    WHERE HireDate > @hireDate
);

SELECT * FROM dbo.GetEmployeesHiredAfter('2022-01-01');

String Functions

String functions manipulate string data.

• CONCAT(string1, string2, ...): Concatenates strings.
• SUBSTRING(string, start, length): Extracts a substring.
• REPLACE(string, old_substring, new_substring): Replaces occurrences of a substring.
• TRIM(string): Removes leading and trailing spaces.
• LEFT(string, length): Returns the left part of a string.
• RIGHT(string, length): Returns the right part of a string.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

Date and Time Functions

Date and time functions work with date and time values.

• DATEADD(datepart, number, date): Adds a specified time interval to a date.
• DATEDIFF(datepart, startdate, enddate): Returns the difference between two dates.
• YEAR(date): Extracts the year from a date.
• MONTH(date): Extracts the month from a date.
• DAY(date): Extracts the day from a date.
• FORMAT(date, format_string): Formats a date value.

SELECT DATEDIFF(day, '2023-01-01', '2023-03-15') AS DaysDifference;

Numeric Functions

Numeric functions perform mathematical operations.

• ABS(number): Returns the absolute value of a number.
• CEILING(number): Returns the smallest integer greater than or equal to a number.
• FLOOR(number): Returns the largest integer less than or equal to a number.
• RAND(): Returns a random floating-point number.
• SQRT(number): Returns the square root of a number.

SELECT CEILING(10.5 / 2);

System Functions

System functions return information about the database environment or the current user.

• DB_NAME(): Returns the name of the current database.
• USER_NAME(): Returns the login name of the current user.
• @@ROWCOUNT: Returns the number of rows affected by the last statement.
• NEWID(): Returns a unique identifier (GUID).

SELECT DB_NAME();

JSON Functions

JSON functions allow you to work with JSON data stored in SQL Server.

• ISJSON(string): Checks if a string contains valid JSON.
• JSON_VALUE(string, path): Extracts a scalar value from a JSON string.
• JSON_QUERY(string, path): Extracts an object or array from a JSON string.
• JSON_MODIFY(string, path, new_value): Updates a value in a JSON string.

DECLARE @json NVARCHAR(100) = '{"name": "John Doe", "age": 30}';
SELECT JSON_VALUE(@json, '$.name');

XML Functions

XML functions allow you to work with XML data stored in SQL Server.

• XMLELEMENT(): Creates an XML element.
• XMLATTVALUE(): Creates an XML attribute.
• query() method on XML data type: Selects XML nodes.

-- Assuming you have an XML column named 'ProductDescription' in a table 'Products'
SELECT
    T.c.value('ProductName[1]', 'VARCHAR(100)') AS ProductName,
    T.c.value('Color[1]', 'VARCHAR(50)') AS Color
FROM
    Products
CROSS APPLY
    ProductDescription.nodes('/Product/Details') AS T(c);

This overview covers common types of SQL functions. For detailed syntax and advanced usage specific to your SQL Server version, please refer to the official Microsoft documentation.