T-SQL Functions

Functions are routines that accept parameters, perform an action, and return a value. SQL Server provides built-in functions for a wide range of purposes, and you can also create your own user-defined functions.

Built-in Functions

SQL Server offers a rich set of built-in functions categorized by their purpose:

Aggregate Functions

These functions perform a calculation on a set of values and return a single value.

AVG(): Returns the average value of a column.
COUNT(): Returns the number of rows or non-null values.
MAX(): Returns the maximum value in a set.
MIN(): Returns the minimum value in a set.
SUM(): Returns the sum of values in a column.
STDEV(), STDEVP(), VAR(), VARP(): Statistical functions.

Scalar Functions

These functions return a single value based on the input provided. They can operate on string, date, numeric, and other data types.

String Functions: LEN(), SUBSTRING(), REPLACE(), UPPER(), LOWER(), CONCAT(), etc.
Date and Time Functions: GETDATE(), DATEADD(), DATEDIFF(), FORMAT(), EOMONTH(), etc.
Numeric Functions: ABS(), CEILING(), FLOOR(), ROUND(), RAND(), etc.
System Functions: @@ROWCOUNT, DB_NAME(), USER_NAME(), etc.
JSON Functions: ISJSON(), JSON_VALUE(), JSON_QUERY(), OPENJSON(), etc.
XML Functions: XACT_ARTICLE(), XML_INDEX_PROJECT(), etc.

Table-Valued Functions

These functions return a table as a result. They can be inline or multi-statement.

System Table-Valued Functions: sys.dm_exec_sessions, sys.dm_exec_requests, etc.
User-Defined Table-Valued Functions: Created by the user.

User-Defined Functions (UDFs)

You can create your own functions to encapsulate complex logic or reusable computations.

Types of UDFs:

Scalar UDFs: Return a single value.
Inline Table-Valued Functions (ITVF): Return a table and are defined with a single RETURN statement. They generally offer better performance than multi-statement TVFs.
Multi-Statement Table-Valued Functions (MSTVF): Return a table and can contain multiple T-SQL statements to populate the return table.

Creating a User-Defined Function:

The basic syntax for creating a function is:

CREATE FUNCTION schema_name.function_name
(
    @parameter1 data_type,
    @parameter2 data_type = default_value
)
RETURNS return_type
[WITH SCHEMABINDING]
AS
BEGIN
    -- T-SQL statements to perform the function's logic
    DECLARE @result data_type;
    -- ... calculation ...
    SET @result = ...;
    RETURN @result;
END;

Example: Scalar UDF

CalculateTotalSalary

Calculates the total salary for an employee, including a bonus.

CREATE FUNCTION dbo.CalculateTotalSalary (@baseSalary DECIMAL(10, 2), @bonus DECIMAL(10, 2) = 0)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @baseSalary + @bonus;
END;

Usage:

SELECT dbo.CalculateTotalSalary(50000, 5000) AS TotalSalary;

Example: Inline Table-Valued Function

GetProductsByCategory

Returns all products belonging to a specified category.

CREATE FUNCTION dbo.GetProductsByCategory (@CategoryID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT ProductID, ProductName, UnitPrice
    FROM Production.Products
    WHERE CategoryID = @CategoryID
);

Usage:

SELECT * FROM dbo.GetProductsByCategory(1);

Key Considerations

Performance: Be mindful of the performance implications, especially with scalar UDFs in the WHERE clause or SELECT list, as they can execute row by row.
Determinism: Understand whether a function is deterministic (always returns the same result for the same input) or non-deterministic.
Reusability: Use functions to simplify complex queries and promote code reuse.
Permissions: Ensure appropriate permissions are granted to execute functions.

For detailed information on specific functions, refer to the official Microsoft documentation.