T-SQL Functions

Explore the comprehensive list of Transact-SQL (T-SQL) functions available in Azure SQL Database. These functions perform operations on input values and return a single value of a specified type. They are categorized for easier navigation.

Aggregate Functions

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

AVG()

Calculates the average value of a column.

AVG(expression)

COUNT()

Counts the number of rows or non-null values in a column.

COUNT([ALL | DISTINCT] expression)

MAX()

Returns the maximum value in a set.

MAX(expression)

MIN()

Returns the minimum value in a set.

MIN(expression)

SUM()

Calculates the sum of values in a column.

SUM(expression)

GROUP_CONCAT()

Concatenates values from multiple rows into a single string (MySQL compatibility).

GROUP_CONCAT([ALL | DISTINCT] expression [ORDER BY ...] [SEPARATOR ...])

Scalar Functions

Scalar functions return a single value for each row processed by a query.

String Functions

Manipulate string data.

LEN()

Returns the length of a string.

LEN(string_expression)

LEFT()

Returns the left part of a string.

LEFT(character_expression, integer_expression)

RIGHT()

Returns the right part of a string.

RIGHT(character_expression, integer_expression)

SUBSTRING()

Extracts a part of a string.

SUBSTRING(expression, start, length)

UPPER()

Converts a string to uppercase.

UPPER(character_expression)

LOWER()

Converts a string to lowercase.

LOWER(character_expression)

REPLACE()

Replaces occurrences of a substring.

REPLACE(string_expression, string_to_replace, replacement_string)

Date and Time Functions

Work with date and time values.

GETDATE()

Returns the current database system timestamp.

GETDATE()

DATEPART()

Returns a specified part of a date.

DATEPART(datepart, date)

DATEDIFF()

Returns the difference between two dates.

DATEDIFF(datepart, startdate, enddate)

DATEADD()

Adds or subtracts a specified time interval from a date.

DATEADD(datepart, number, date)

FORMAT()

Formats a value as a string with a specified format.

FORMAT(value, format [, culture])

Numeric Functions

Perform mathematical operations.

ABS()

Returns the absolute value of a number.

ABS(numeric_expression)

ROUND()

Rounds a numeric value.

ROUND(numeric_expression, length [, function])

CEILING()

Returns the smallest integer greater than or equal to a number.

CEILING(numeric_expression)

FLOOR()

Returns the largest integer less than or equal to a number.

FLOOR(numeric_expression)

System Functions

Provide information about the system or environment.

DB_NAME()

Returns the name of the specified database.

DB_NAME([database_id])

USER_NAME()

Returns the name of the database user.

USER_NAME([user_id])

SCOPE_IDENTITY()

Returns the last identity value inserted into an identity column in the same scope.

SCOPE_IDENTITY()

Table-Valued Functions

Table-valued functions (TVFs) return a table as a result set. They can be used in the FROM clause of a query.

Common system table-valued functions include:

sys.dm_exec_query_stats

Returns aggregate performance statistics for cached execution plans.

sys.dm_exec_query_stats

sys.dm_exec_sessions

Returns information about active user sessions.

sys.dm_exec_sessions

Common Table Expressions (CTEs)

While not strictly functions, CTEs are powerful for structuring complex queries and can be referenced multiple times within a single statement. They provide a temporary named result set.

Example: Using a CTE to find average order value

The following example uses a CTE to calculate the average order value for each customer.


WITH CustomerOrderAverages AS (
    SELECT
        CustomerID,
        AVG(TotalAmount) AS AverageOrderAmount
    FROM Orders
    GROUP BY CustomerID
)
SELECT
    c.CompanyName,
    coa.AverageOrderAmount
FROM Customers c
JOIN CustomerOrderAverages coa ON c.CustomerID = coa.CustomerID
WHERE coa.AverageOrderAmount > 100;

SQL Database Reference