SQL Functions Reference

This section provides comprehensive reference information for Transact-SQL (T-SQL) functions supported by Microsoft SQL Server. Functions are routines that accept parameters, perform an action, and return a single value.

Categorized Functions

Aggregate Functions

These functions perform calculations on a set of rows and return a single value.

  • AVG

    Calculates the average value of an expression.

  • COUNT

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

  • MAX

    Returns the maximum value in a set of values.

  • MIN

    Returns the minimum value in a set of values.

  • SUM

    Calculates the sum of values in a column.

Scalar Functions

These functions perform operations on a single value and return a single value.

String Functions

  • CONCAT

    Concatenates two or more string values.

  • LEN

    Returns the length of a string.

  • LOWER

    Converts a string to lowercase.

  • UPPER

    Converts a string to uppercase.

  • SUBSTRING

    Extracts a portion of a string.

Date and Time Functions

  • GETDATE

    Returns the current database system timestamp.

  • DATEPART

    Returns a specified part of a date as an integer.

  • DATEDIFF

    Returns the difference between two dates in a specified datepart.

Mathematical Functions

  • ABS

    Returns the absolute value of a numeric expression.

  • CEILING

    Returns the smallest integer greater than or equal to a specified numeric expression.

  • FLOOR

    Returns the largest integer less than or equal to a specified numeric expression.

Ranking Functions

These functions assign a rank to each row within a partition of a result set.

  • ROW_NUMBER

    Assigns a unique sequential integer to each row.

  • RANK

    Assigns a rank to each row within a partition, with gaps in rank values.

  • DENSE_RANK

    Assigns a rank to each row within a partition, without gaps in rank values.

  • NTILE

    Divides rows into a specified number of groups.

Window Functions

These functions perform calculations across a set of table rows that are somehow related to the current row.

  • LAG

    Access data from a previous row in the same result set.

  • LEAD

    Access data from a subsequent row in the same result set.

  • FIRST_VALUE

    Returns the value of the expression for the first row in the window frame.

  • LAST_VALUE

    Returns the value of the expression for the last row in the window frame.

Example Usage

Using the CONCAT function

SELECT CONCAT('Microsoft', ' SQL Server') AS FullName;

Using the AVG function

SELECT AVG(ListPrice) AS AveragePrice FROM Production.Product;