User-Defined Functions (UDFs) in SQL Server are programmable routines that allow you to encapsulate reusable logic within the database. They can simplify complex queries, enhance readability, and promote code reuse. There are three main types of UDFs:

1. Scalar Functions

These functions return a single value of a specific data type. They can be used wherever an expression is allowed. Here's a basic example:

CREATE FUNCTION dbo.GetEmployeeFullName (@EmployeeID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @FullName NVARCHAR(100);
    SELECT @FullName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
    RETURN @FullName;
END;

2. Inline Table-Valued Functions (ITVF)

These functions return a table data type, and the result is defined by a single SELECT statement. They are similar to views but can accept parameters. Here's an example:

CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

3. Multi-Statement Table-Valued Functions (MSTVF)

These functions return a table, but unlike ITVF, they allow multiple statements to define the result. Here' an example:

CREATE FUNCTION dbo.GetTopPaidEmployees (@TopN INT)
RETURNS @TopPaidEmployees TABLE (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2)
)
AS
BEGIN
    INSERT INTO @TopPaidEmployees
    SELECT TOP (@TopN) EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC;
    RETURN;
END;

Advantages of UDFs

• Reusability: Encapsulate common logic that can be reused across multiple queries.

• Maintainability: Simplify complex queries and make them easier to maintain.

• Performance: Sometimes UDFs can improve performance by reducing code redundancy and optimizing logic execution.

Limitations

• Performance Overhead: Scalar and MSTVFs can introduce performance overhead, especially with large datasets.

• Limited Functionality: UDFs have some limitations, such as not allowing non-deterministic functions like GETDATE() within the function body.

Would you like a deeper dive into any specific type of UDF or a practical example of how to use them in a particular scenario?