All 7 User Defined Functions questions
More in User Defined Functions
What is the difference between an inline table valued function and a multi statement table valued function? Why can calling a scalar user defined function within a query against a large table cause significant performance problems? What improvements did SQL Server 2019 introduce for scalar function performance through a feature called scalar UDF inlining? What restrictions exist on what a user defined function is allowed to do compared to a stored procedure? How would you design a set of reusable functions for a data warehouse to standardize common calculations like currency conversion or fiscal period determination? What are the tradeoffs between implementing complex reusable business logic as a user defined function versus building it directly into application code?
Advertisement
Back to results for “SQL Queries”
SQL Server › User Defined Functions

What is a user defined function in SQL Server, and why would you create one instead of repeating the same logic in multiple queries?

Beginner

Answer

A user defined function lets you package a piece of reusable logic, such as a calculation or a formatting routine, into a single named object that you can call from many different queries, avoiding the need to repeat and maintain the same complex logic in multiple places throughout your codebase.
CREATE FUNCTION dbo.CalculateAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
  RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;

SELECT Name, dbo.CalculateAge(BirthDate) AS Age FROM Customers;
Real-world example A customer reporting system uses a single reusable function to calculate age from a birth date, ensuring every report across the entire application calculates age exactly the same consistent way.

Common follow-ups: What happens if the logic inside a function needs to change later?;Can a user defined function call another user defined function?

Stored Procedures & Functions;Data Types & Schema Design
All 7 User Defined Functions questions