SQL Function

← Previous Next →

SQL functions are reusable blocks of code that perform a specific task (e.g., a complex calculation) and return a value or set of values. It can be used inside SELECT, WHERE, JOIN. Functions are categorized into built-in system functions and user-defined functions (UDFs).

1. Built-in Functions:
Aggregate (AVG, COUNT, SUM, MIN, MAX): Summarize data sets
String (CONCAT, LEN, SUBSTRING): Manipulate text data.
Date/Time (GETDATE, DATEPART, DATEDIFF): Handle temporal data.
Math (ROUND, ABS, CEILING): Perform calculations.
Conversion (CAST, CONVERT): Change data type

2. User-Defined Functions (UDFs):Users can define custom T-SQL or CLR routines for modularity.
1️⃣ Scalar Function: Return a single value.
2️⃣ Table-Valued Function (Inline)
3️⃣ Multi-Statement Table-Valued Function

Example

CREATE FUNCTION dbo.GetFullName
(
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50)
)
RETURNS VARCHAR(150)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;


Performance Rule:
If performance matters:
✅ Prefer Inline Table-Valued Function
❌ Avoid Scalar & Multi-Statement TVF on large datasets

← Previous Next →
Topics