SQL Server Interview Question #113

What is a scalar user-defined function?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

A scalar UDF returns a single value. It can encapsulate reusable calculations or formatting logic and can be called from queries where a scalar expression is valid.

Historically, scalar UDFs could be expensive when executed row by row. Modern SQL Server versions can inline eligible scalar UDFs under certain conditions, but developers should still measure performance and avoid assuming that a function call is free.

Code Example

CREATE FUNCTION dbo.CalculateDiscountedPrice
(
    @Price DECIMAL(18,2),
    @DiscountPercent DECIMAL(5,2)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
    RETURN @Price - (@Price * @DiscountPercent / 100.0);
END;
GO

SELECT dbo.CalculateDiscountedPrice(1000, 10);