Functions

Built-In Functions

SQL Server built-in functions are either deterministic or nondeterministic.

Deterministic Functions	Non-Deterministic Functions
Functions are deterministic when they always return the same result anytime they're called by using a specific set of input values.	Functions are nondeterministic when they could return different results every time they're called, even with the same specific set of input values.

• Aggregate Functions - An aggregate function performs a calculation on a set of values, and returns a single value.

Examples: AVG(), COUNT(), MAX(), MIN(), SUM()

• Ranking Functions - Ranking functions return a ranking value for each row in a partition.

Examples: DENSERANK(), NTILE(), RANK(), ROW_NUMBER()

• Scaler Functions - Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid.

  • Date and Time Functions

• Error Functions

Examples: ERROR_LINE(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_MESSAGE()

User-Defined Functions

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions{
    [SqlFunction]
    public static float InchtoMillimeters(float inches){
        return 25.4f * inches;
    }
}