SQL Language Reference - Functions

Explore the built-in functions available in SQL for data manipulation and querying.

Categories of Functions

SQL functions are categorized based on their purpose and the type of data they operate on. Understanding these categories helps in efficiently finding the right function for your needs.

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.

Common Aggregate Functions:

  • AVG(expression): Returns the average value.
  • COUNT(expression): Returns the number of rows.
  • MAX(expression): Returns the maximum value.
  • MIN(expression): Returns the minimum value.
  • SUM(expression): Returns the sum of values.

Example:

SELECT AVG(Salary) AS AverageSalary
FROM Employees
WHERE Department = 'Sales';

Scalar Functions

Scalar functions operate on a single input value and return a single output value. They can be used in various parts of a SQL statement.

Common Scalar Function Types:

  • String Functions: Manipulate string data (e.g., LEN(), SUBSTRING(), CONCAT()).
  • Numeric Functions: Perform mathematical operations (e.g., ROUND(), ABS(), CEILING()).
  • Date and Time Functions: Work with dates and times (e.g., GETDATE(), DATEADD(), DATEDIFF()).
  • Conversion Functions: Convert values from one data type to another (e.g., CAST(), CONVERT()).

Example: String Concatenation

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Customers
WHERE CustomerID = 10;

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. This is similar to aggregate functions, but they do not cause rows to be collapsed into a single output row. The row set to be processed by a window function is called a "window frame".

Common Window Functions:

  • ROW_NUMBER(): Assigns a unique sequential integer to each row.
  • RANK(): Assigns a rank to each row within its partition.
  • DENSE_RANK(): Assigns a rank to each row within its partition, with no gaps in rank values.
  • LAG(): Accesses data from a previous row.
  • LEAD(): Accesses data from a subsequent row.
  • NTILE(n): Divides rows into a specified number of groups.

Example: Ranking Sales

SELECT
    ProductName,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank
FROM Sales;

Table-Valued Functions (TVFs)

Table-valued functions return a table as their result. They can be used in the FROM clause of a query, similar to a view or a base table.

Types of TVFs:

  • Inline TVFs: Contain a single SELECT statement.
  • Multi-statement TVFs: Can contain multiple SQL statements and complex logic.

Example:

-- Assuming a TVF named GetOrdersByCustomer exists
SELECT *
FROM dbo.GetOrdersByCustomer(101);

System Functions

System functions provide information about the database system, connections, or other metadata.

Examples:

  • DB_NAME(): Returns the name of the current database.
  • SUSER_NAME(): Returns the login name for a security identifier.
  • @@ROWCOUNT: Returns the number of rows affected by the last statement.

Example:

SELECT DB_NAME() AS CurrentDatabase;