SQL Functions: Complete Guide with Examples
Comprehensive guide to SQL functions including scalar functions, table-valued functions, built-in functions, user-defined functions, and performance best practices
SQL Functions
Table of Contents
- Introduction to Functions
- Scalar Functions
- Table-Valued Functions
- Inline Table-Valued Functions
- Multi-Statement Table-Valued Functions
- Built-in Functions
- User-Defined Functions
- Deterministic vs Non-Deterministic
- Best Practices
Introduction to Functions
Functions are database objects that accept parameters, perform operations, and return a single value or a table. Unlike stored procedures, functions can be used in SQL statements.
Functions vs Stored Procedures
┌──────────────────────────────────────────────────────────┐
│ FUNCTIONS vs STORED PROCEDURES │
├──────────────────────────────────────────────────────────┤
│ │
│ Functions: │
│ ✅ Must return a value │
│ ✅ Can be used in SELECT statements │
│ ✅ Cannot modify database state (usually) │
│ ✅ Can be deterministic │
│ ❌ Limited error handling │
│ ❌ Cannot use transactions │
│ │
│ Stored Procedures: │
│ ✅ Optional return value │
│ ✅ Can modify database state │
│ ✅ Full error handling │
│ ✅ Can use transactions │
│ ❌ Cannot be used in SELECT │
│ ❌ Called with CALL/EXEC │
│ │
└──────────────────────────────────────────────────────────┘
Function Types
┌──────────────────────────────────────────────────────────┐
│ FUNCTION TYPES │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. Scalar Functions │
│ - Return single value │
│ - Can be used in expressions │
│ - Example: UPPER(), ROUND(), custom functions │
│ │
│ 2. Table-Valued Functions │
│ - Return table │
│ - Can be used in FROM clause │
│ - Two types: Inline and Multi-statement │
│ │
│ 3. Aggregate Functions │
│ - Operate on sets of values │
│ - Return single value │
│ - Example: SUM(), AVG(), COUNT() │
│ │
│ 4. Window Functions │
│ - Operate on window of rows │
│ - Return value for each row │
│ - Example: ROW_NUMBER(), RANK() │
│ │
└──────────────────────────────────────────────────────────┘
Function Execution Flow:
┌─────────────────────────────────────────────────────────────┐
│ FUNCTION EXECUTION FLOW │
├─────────────────────────────────────────────────────────────┤
│ │
│ Scalar Function Execution: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ SELECT statement with function │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ SELECT dbo.MyFunc(column) │ │ │
│ │ │ FROM table │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ For Each Row: │ │ │
│ │ │ │ │ │
│ │ │ 1. Pass parameter value │ │ │
│ │ │ ↓ │ │ │
│ │ │ 2. Execute function logic │ │ │
│ │ │ ↓ │ │ │
│ │ │ 3. Return single value │ │ │
│ │ │ ↓ │ │ │
│ │ │ 4. Use in result set │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Return Complete Result Set │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Table-Valued Function Execution: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ SELECT statement with TVF │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ SELECT * FROM │ │ │
│ │ │ dbo.GetOrders(@customer_id) │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Execute Function Once │ │ │
│ │ │ │ │ │
│ │ │ 1. Pass parameters │ │ │
│ │ │ ↓ │ │ │
│ │ │ 2. Execute query/logic │ │ │
│ │ │ ↓ │ │ │
│ │ │ 3. Build result table │ │ │
│ │ │ ↓ │ │ │
│ │ │ 4. Return entire table │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Use Table in Query │ │ │
│ │ │ - JOIN with other tables │ │ │
│ │ │ - Apply WHERE filters │ │ │
│ │ │ - Aggregate results │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Performance Comparison: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Inline TVF: │ │
│ │ Query → [Inline Function] → Optimized Plan │ │
│ │ ⚡ Fast - Treated like a view │ │
│ │ │ │
│ │ Multi-Statement TVF: │ │
│ │ Query → [Function Logic] → Table Variable → Result │ │
│ │ ⚠️ Slower - Materialized table │ │
│ │ │ │
│ │ Scalar Function (in SELECT): │ │
│ │ Query → [Function × N rows] → Result │ │
│ │ ⚠️ Can be slow on large tables │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Scalar Functions
Scalar functions return a single value based on input parameters.
Example 1: Simple Scalar Function (MySQL)
-- MySQL: Calculate discount
DELIMITER //
CREATE FUNCTION CalculateDiscount(
p_amount DECIMAL(10,2),
p_discount_percent DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN p_amount * (p_discount_percent / 100);
END //
DELIMITER ;
-- Use in SELECT
SELECT
product_name,
price,
CalculateDiscount(price, 10) AS discount_amount,
price - CalculateDiscount(price, 10) AS final_price
FROM products;
Example 2: Scalar Function (SQL Server)
-- SQL Server: Calculate age
CREATE FUNCTION dbo.CalculateAge(
@birth_date DATE
)
RETURNS INT
AS
BEGIN
DECLARE @age INT;
SET @age = DATEDIFF(YEAR, @birth_date, GETDATE()) -
CASE
WHEN DATEADD(YEAR, DATEDIFF(YEAR, @birth_date, GETDATE()), @birth_date) > GETDATE()
THEN 1
ELSE 0
END;
RETURN @age;
END;
-- Use in query
SELECT
first_name,
last_name,
birth_date,
dbo.CalculateAge(birth_date) AS age
FROM employees;
Example 3: String Manipulation Function
-- MySQL: Format phone number
DELIMITER //
CREATE FUNCTION FormatPhoneNumber(
p_phone VARCHAR(20)
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE v_formatted VARCHAR(20);
DECLARE v_digits VARCHAR(20);
-- Remove non-digits
SET v_digits = REGEXP_REPLACE(p_phone, '[^0-9]', '');
-- Format as (XXX) XXX-XXXX
IF LENGTH(v_digits) = 10 THEN
SET v_formatted = CONCAT(
'(', SUBSTRING(v_digits, 1, 3), ') ',
SUBSTRING(v_digits, 4, 3), '-',
SUBSTRING(v_digits, 7, 4)
);
ELSE
SET v_formatted = p_phone;
END IF;
RETURN v_formatted;
END //
DELIMITER ;
-- Use function
SELECT
customer_id,
first_name,
FormatPhoneNumber(phone) AS formatted_phone
FROM customers;
Example 4: Mathematical Function
-- MySQL: Calculate compound interest
DELIMITER //
CREATE FUNCTION CalculateCompoundInterest(
p_principal DECIMAL(10,2),
p_rate DECIMAL(5,4),
p_years INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN p_principal * POWER(1 + p_rate, p_years);
END //
DELIMITER ;
-- Use function
SELECT
CalculateCompoundInterest(1000, 0.05, 10) AS future_value;
-- Returns: 1628.89
Example 5: Business Logic Function
-- SQL Server: Calculate shipping cost
CREATE FUNCTION dbo.CalculateShippingCost(
@weight DECIMAL(10,2),
@distance INT,
@is_express BIT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @base_cost DECIMAL(10,2);
DECLARE @weight_cost DECIMAL(10,2);
DECLARE @distance_cost DECIMAL(10,2);
DECLARE @express_fee DECIMAL(10,2);
SET @base_cost = 5.00;
SET @weight_cost = @weight * 0.50;
SET @distance_cost = @distance * 0.10;
SET @express_fee = CASE WHEN @is_express = 1 THEN 10.00 ELSE 0.00 END;
RETURN @base_cost + @weight_cost + @distance_cost + @express_fee;
END;
-- Use in query
SELECT
order_id,
weight,
distance,
is_express,
dbo.CalculateShippingCost(weight, distance, is_express) AS shipping_cost
FROM orders;
Table-Valued Functions
Table-valued functions return a table that can be used in FROM clauses.
Types of Table-Valued Functions
┌──────────────────────────────────────────────────────────┐
│ TABLE-VALUED FUNCTION TYPES │
├──────────────────────────────────────────────────────────┤
│ │
│ Inline Table-Valued Functions: │
│ - Single SELECT statement │
│ - Better performance │
│ - Acts like a parameterized view │
│ - No BEGIN/END block │
│ │
│ Multi-Statement Table-Valued Functions: │
│ - Multiple statements │
│ - Declare table variable │
│ - More complex logic │
│ - Uses BEGIN/END block │
│ │
└──────────────────────────────────────────────────────────┘
Scalar vs Table-Valued Functions Comparison:
┌─────────────────────────────────────────────────────────────┐
│ SCALAR vs TABLE-VALUED FUNCTIONS │
├─────────────────────────────────────────────────────────────┤
│ │
│ Scalar Function: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Input: Single/Multiple Parameters │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ @param1, @param2, @param3 │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Function Logic │ │ │
│ │ │ - Calculations │ │ │
│ │ │ - String operations │ │ │
│ │ │ - Conditional logic │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Output: Single Value │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ RETURN single_value │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ │ Usage: │ │
│ │ • SELECT column, dbo.Func(column) FROM table │ │
│ │ • WHERE dbo.Func(column) > 100 │ │
│ │ • ORDER BY dbo.Func(column) │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Table-Valued Function (Inline): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Input: Parameters │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ @customer_id, @date_range │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Single SELECT Statement │ │ │
│ │ │ RETURN ( │ │ │
│ │ │ SELECT ... FROM ... WHERE ... │ │ │
│ │ │ ) │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Output: Table (Multiple Rows/Columns) │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ col1 │ col2 │ col3 │ col4 │ │ │
│ │ ├──────┼──────┼──────┼────── │ │ │
│ │ │ val1 │ val2 │ val3 │ val4 │ │ │
│ │ │ val1 │ val2 │ val3 │ val4 │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ │ Usage: │ │
│ │ • SELECT * FROM dbo.GetOrders(@id) │ │
│ │ • JOIN dbo.GetOrders(@id) o ON ... │ │
│ │ • CROSS APPLY dbo.GetOrders(t.id) │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Table-Valued Function (Multi-Statement): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Input: Parameters │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ @start_date, @end_date │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Declare Table Variable │ │ │
│ │ │ @result TABLE (...) │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Multiple Statements │ │ │
│ │ │ - Variables │ │ │
│ │ │ - Loops │ │ │
│ │ │ - Conditional logic │ │ │
│ │ │ - INSERT INTO @result │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Output: Table Variable │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ RETURN @result │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ │ Usage: Same as Inline TVF │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Key Differences: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Feature │ Scalar │ Inline TVF │ Multi TVF │ │
│ │ ─────────────────┼────────┼────────────┼────────── │ │
│ │ Return Type │ Value │ Table │ Table │ │
│ │ Performance │ ⚠️ │ ⚡ │ ⚠️ │ │
│ │ Optimization │ Limited│ Full │ Limited │ │
│ │ Complexity │ Simple │ Simple │ Complex │ │
│ │ Use in SELECT │ ✅ │ ❌ │ ❌ │ │
│ │ Use in FROM │ ❌ │ ✅ │ ✅ │ │
│ │ Multiple Stmts │ ✅ │ ❌ │ ✅ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Inline Table-Valued Functions
Example 1: Basic Inline TVF (SQL Server)
-- SQL Server: Get customer orders
CREATE FUNCTION dbo.GetCustomerOrders(
@customer_id INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
o.order_id,
o.order_date,
o.total_amount,
o.status,
COUNT(oi.order_item_id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = @customer_id
GROUP BY o.order_id, o.order_date, o.total_amount, o.status
);
-- Use in query
SELECT * FROM dbo.GetCustomerOrders(1);
-- Join with other tables
SELECT
c.first_name,
c.last_name,
co.*
FROM customers c
CROSS APPLY dbo.GetCustomerOrders(c.customer_id) co
WHERE co.total_amount > 100;
Example 2: Inline TVF with Date Range
-- SQL Server: Get orders in date range
CREATE FUNCTION dbo.GetOrdersByDateRange(
@start_date DATE,
@end_date DATE
)
RETURNS TABLE
AS
RETURN
(
SELECT
o.order_id,
o.customer_id,
c.first_name,
c.last_name,
o.order_date,
o.total_amount,
o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN @start_date AND @end_date
);
-- Use function
SELECT * FROM dbo.GetOrdersByDateRange('2023-01-01', '2023-12-31')
ORDER BY order_date DESC;
Example 3: Inline TVF with Calculations
-- SQL Server: Calculate customer statistics
CREATE FUNCTION dbo.GetCustomerStats(
@min_orders INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order_value,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) >= @min_orders
);
-- Use function
SELECT * FROM dbo.GetCustomerStats(5)
ORDER BY total_spent DESC;
Multi-Statement Table-Valued Functions
Example 1: Basic Multi-Statement TVF
-- SQL Server: Generate number sequence
CREATE FUNCTION dbo.GenerateNumbers(
@start INT,
@end INT
)
RETURNS @numbers TABLE (
num INT
)
AS
BEGIN
DECLARE @current INT = @start;
WHILE @current <= @end
BEGIN
INSERT INTO @numbers VALUES (@current);
SET @current = @current + 1;
END
RETURN;
END;
-- Use function
SELECT * FROM dbo.GenerateNumbers(1, 10);
Example 2: Complex Business Logic TVF
-- SQL Server: Calculate order summary with discounts
CREATE FUNCTION dbo.GetOrderSummary(
@order_id INT
)
RETURNS @summary TABLE (
order_id INT,
subtotal DECIMAL(10,2),
discount DECIMAL(10,2),
tax DECIMAL(10,2),
total DECIMAL(10,2),
discount_reason VARCHAR(100)
)
AS
BEGIN
DECLARE @subtotal DECIMAL(10,2);
DECLARE @discount DECIMAL(10,2);
DECLARE @discount_reason VARCHAR(100);
DECLARE @tax DECIMAL(10,2);
DECLARE @total DECIMAL(10,2);
DECLARE @tax_rate DECIMAL(5,4) = 0.0825;
-- Calculate subtotal
SELECT @subtotal = SUM(quantity * unit_price)
FROM order_items
WHERE order_id = @order_id;
-- Determine discount
IF @subtotal >= 1000
BEGIN
SET @discount = @subtotal * 0.15;
SET @discount_reason = '15% discount for orders over $1000';
END
ELSE IF @subtotal >= 500
BEGIN
SET @discount = @subtotal * 0.10;
SET @discount_reason = '10% discount for orders over $500';
END
ELSE
BEGIN
SET @discount = 0;
SET @discount_reason = 'No discount applied';
END
-- Calculate tax and total
SET @tax = (@subtotal - @discount) * @tax_rate;
SET @total = @subtotal - @discount + @tax;
-- Insert result
INSERT INTO @summary
VALUES (@order_id, @subtotal, @discount, @tax, @total, @discount_reason);
RETURN;
END;
-- Use function
SELECT * FROM dbo.GetOrderSummary(1);
Example 3: Hierarchical Data TVF
-- SQL Server: Get employee hierarchy
CREATE FUNCTION dbo.GetEmployeeHierarchy(
@manager_id INT
)
RETURNS @hierarchy TABLE (
employee_id INT,
employee_name VARCHAR(100),
level INT,
path VARCHAR(1000)
)
AS
BEGIN
-- Insert root manager
INSERT INTO @hierarchy
SELECT
employee_id,
first_name + ' ' + last_name,
0,
CAST(employee_id AS VARCHAR(1000))
FROM employees
WHERE employee_id = @manager_id;
-- Insert subordinates recursively
DECLARE @level INT = 0;
WHILE EXISTS (
SELECT 1 FROM employees e
INNER JOIN @hierarchy h ON e.manager_id = h.employee_id
WHERE h.level = @level
)
BEGIN
SET @level = @level + 1;
INSERT INTO @hierarchy
SELECT
e.employee_id,
e.first_name + ' ' + e.last_name,
@level,
h.path + '/' + CAST(e.employee_id AS VARCHAR(10))
FROM employees e
INNER JOIN @hierarchy h ON e.manager_id = h.employee_id
WHERE h.level = @level - 1;
END
RETURN;
END;
-- Use function
SELECT * FROM dbo.GetEmployeeHierarchy(1)
ORDER BY level, employee_name;
Built-in Functions
SQL provides many built-in functions for common operations.
String Functions
-- String manipulation examples
SELECT
-- Case conversion
UPPER('hello') AS uppercase, -- HELLO
LOWER('WORLD') AS lowercase, -- world
-- Substring
SUBSTRING('Hello World', 1, 5) AS sub, -- Hello
LEFT('Hello World', 5) AS left_part, -- Hello
RIGHT('Hello World', 5) AS right_part, -- World
-- Length
LENGTH('Hello') AS len, -- 5 (MySQL)
LEN('Hello') AS len, -- 5 (SQL Server)
-- Trim
TRIM(' Hello ') AS trimmed, -- Hello
LTRIM(' Hello') AS left_trim, -- Hello
RTRIM('Hello ') AS right_trim, -- Hello
-- Concatenation
CONCAT('Hello', ' ', 'World') AS concat, -- Hello World
'Hello' || ' ' || 'World' AS concat2, -- Hello World (PostgreSQL)
-- Replace
REPLACE('Hello World', 'World', 'SQL') AS replaced, -- Hello SQL
-- Position
POSITION('World' IN 'Hello World') AS pos, -- 7
INSTR('Hello World', 'World') AS pos2; -- 7 (MySQL)
Numeric Functions
-- Mathematical operations
SELECT
-- Rounding
ROUND(123.456, 2) AS rounded, -- 123.46
CEILING(123.456) AS ceiling, -- 124
FLOOR(123.456) AS floor, -- 123
TRUNCATE(123.456, 2) AS truncated, -- 123.45
-- Absolute value
ABS(-123) AS absolute, -- 123
-- Power and root
POWER(2, 3) AS power, -- 8
SQRT(16) AS square_root, -- 4
-- Trigonometric
SIN(PI()/2) AS sine, -- 1
COS(0) AS cosine, -- 1
-- Random
RAND() AS random, -- Random between 0 and 1
-- Sign
SIGN(-123) AS sign; -- -1
Date Functions
-- Date and time operations
SELECT
-- Current date/time
CURDATE() AS current_date, -- 2023-06-01
CURTIME() AS current_time, -- 14:30:00
NOW() AS current_datetime, -- 2023-06-01 14:30:00
-- Extract parts
YEAR(NOW()) AS year, -- 2023
MONTH(NOW()) AS month, -- 6
DAY(NOW()) AS day, -- 1
HOUR(NOW()) AS hour, -- 14
-- Date arithmetic
DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month,
DATEDIFF('2023-12-31', '2023-01-01') AS days_diff,
-- Formatting
DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted,
DATE_FORMAT(NOW(), '%W, %M %d, %Y') AS long_format;
Aggregate Functions
-- Aggregate operations
SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
STDDEV(salary) AS salary_stddev,
VARIANCE(salary) AS salary_variance
FROM employees
GROUP BY department;
Conditional Functions
-- Conditional logic
SELECT
product_name,
price,
stock_quantity,
-- CASE expression
CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 10 THEN 'Low Stock'
WHEN stock_quantity < 50 THEN 'In Stock'
ELSE 'High Stock'
END AS stock_status,
-- IF function (MySQL)
IF(stock_quantity > 0, 'Available', 'Unavailable') AS availability,
-- COALESCE (return first non-null)
COALESCE(discount_price, price) AS final_price,
-- NULLIF (return NULL if equal)
NULLIF(stock_quantity, 0) AS non_zero_stock,
-- IFNULL / ISNULL
IFNULL(discount_price, 0) AS discount
FROM products;
User-Defined Functions
Example 1: Custom Validation Function
-- MySQL: Email validation
DELIMITER //
CREATE FUNCTION IsValidEmail(
p_email VARCHAR(100)
)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
RETURN p_email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}$';
END //
DELIMITER ;
-- Use in query
SELECT
customer_id,
email,
IsValidEmail(email) AS is_valid
FROM customers;
-- Use in WHERE clause
SELECT * FROM customers
WHERE IsValidEmail(email) = FALSE;
Example 2: Custom Formatting Function
-- SQL Server: Format currency
CREATE FUNCTION dbo.FormatCurrency(
@amount DECIMAL(10,2),
@currency_code VARCHAR(3)
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @formatted VARCHAR(20);
SET @formatted = CASE @currency_code
WHEN 'USD' THEN '$' + FORMAT(@amount, 'N2')
WHEN 'EUR' THEN '€' + FORMAT(@amount, 'N2')
WHEN 'GBP' THEN '£' + FORMAT(@amount, 'N2')
ELSE FORMAT(@amount, 'N2') + ' ' + @currency_code
END;
RETURN @formatted;
END;
-- Use function
SELECT
order_id,
total_amount,
dbo.FormatCurrency(total_amount, 'USD') AS formatted_total
FROM orders;
Example 3: Custom Calculation Function
-- MySQL: Calculate working days between dates
DELIMITER //
CREATE FUNCTION CalculateWorkingDays(
p_start_date DATE,
p_end_date DATE
)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v_days INT;
DECLARE v_working_days INT;
DECLARE v_current_date DATE;
DECLARE v_day_of_week INT;
SET v_working_days = 0;
SET v_current_date = p_start_date;
WHILE v_current_date <= p_end_date DO
SET v_day_of_week = DAYOFWEEK(v_current_date);
-- Count if not Saturday (7) or Sunday (1)
IF v_day_of_week NOT IN (1, 7) THEN
SET v_working_days = v_working_days + 1;
END IF;
SET v_current_date = DATE_ADD(v_current_date, INTERVAL 1 DAY);
END WHILE;
RETURN v_working_days;
END //
DELIMITER ;
-- Use function
SELECT
CalculateWorkingDays('2023-01-01', '2023-01-31') AS working_days;
Function Performance Impact Diagram:
┌─────────────────────────────────────────────────────────────┐
│ FUNCTION PERFORMANCE IMPACT │
├─────────────────────────────────────────────────────────────┤
│ │
│ Scalar Function in SELECT (Per-Row Execution): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Query: SELECT id, dbo.CalcTax(amount) FROM orders │ │
│ │ │ │
│ │ Execution Pattern: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Row 1: amount=100 → CalcTax(100) → 8.25 │ │ │
│ │ │ Row 2: amount=200 → CalcTax(200) → 16.50 │ │ │
│ │ │ Row 3: amount=150 → CalcTax(150) → 12.38 │ │ │
│ │ │ ... │ │ │
│ │ │ Row N: amount=X → CalcTax(X) → Y │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Performance: O(N) function calls │ │
│ │ ⚠️ 1,000 rows = 1,000 function executions │ │
│ │ ⚠️ 1,000,000 rows = 1,000,000 function executions │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Inline Calculation (Direct): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Query: SELECT id, amount * 0.0825 FROM orders │ │
│ │ │ │
│ │ Execution Pattern: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Single optimized operation on entire set │ │ │
│ │ │ Database engine handles efficiently │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Performance: O(1) operation │ │
│ │ ⚡ Much faster for large datasets │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Function in WHERE Clause (Index Impact): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ❌ Non-Sargable (Cannot use index): │ │
│ │ WHERE YEAR(order_date) = 2023 │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Table Scan Required │ │ │
│ │ │ • Read all rows │ │ │
│ │ │ • Apply function to each │ │ │
│ │ │ • Filter results │ │ │
│ │ │ Performance: Slow on large tables │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ✅ Sargable (Can use index): │ │
│ │ WHERE order_date >= '2023-01-01' │ │
│ │ AND order_date < '2024-01-01' │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Index Seek │ │ │
│ │ │ • Use index to find range │ │ │
│ │ │ • Read only matching rows │ │ │
│ │ │ Performance: Fast │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Performance Metrics Example: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Operation │ 1K rows │ 1M rows │ │
│ │ ──────────────────┼─────────┼────────── │ │
│ │ Inline calc │ 10ms │ 100ms │ │
│ │ Scalar function │ 50ms │ 5000ms │ │
│ │ Inline TVF │ 15ms │ 150ms │ │
│ │ Multi-stmt TVF │ 100ms │ 10000ms │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Deterministic vs Non-Deterministic
Deterministic vs Non-Deterministic Visual Diagram:
┌─────────────────────────────────────────────────────────────┐
│ DETERMINISTIC vs NON-DETERMINISTIC FUNCTIONS │
├─────────────────────────────────────────────────────────────┤
│ │
│ Deterministic Functions: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Definition: Same input → Same output (always) │ │
│ │ │ │
│ │ Example Flow: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Input: CalculateTax(100, 0.08) │ │ │
│ │ │ ↓ │ │ │
│ │ │ Process: 100 * 0.08 │ │ │
│ │ │ ↓ │ │ │
│ │ │ Output: 8.00 │ │ │
│ │ │ │ │ │
│ │ │ Call again with same inputs: │ │ │
│ │ │ Input: CalculateTax(100, 0.08) │ │ │
│ │ │ ↓ │ │ │
│ │ │ Output: 8.00 (SAME RESULT) │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Characteristics: │ │
│ │ ✅ Predictable results │ │
│ │ ✅ Can be indexed │ │
│ │ ✅ Better optimization │ │
│ │ ✅ Cacheable results │ │
│ │ │ │
│ │ Examples: │ │
│ │ • Mathematical operations: ROUND(), ABS(), POWER() │ │
│ │ • String operations: UPPER(), LOWER(), SUBSTRING() │ │
│ │ • Custom calculations: Tax, Discount, etc. │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Non-Deterministic Functions: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Definition: Same input → Different output (varies) │ │
│ │ │ │
│ │ Example Flow: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ Call 1: GetCurrentTime() │ │ │
│ │ │ ↓ │ │ │
│ │ │ Output: 2023-06-01 10:30:00 │ │ │
│ │ │ │ │ │
│ │ │ Call 2: GetCurrentTime() (1 sec later) │ │ │
│ │ │ ↓ │ │ │
│ │ │ Output: 2023-06-01 10:30:01 (DIFFERENT!) │ │ │
│ │ │ │ │ │
│ │ │ Call 3: RAND() │ │ │
│ │ │ ↓ │ │ │
│ │ │ Output: 0.7234 (Random each time) │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Characteristics: │ │
│ │ ⚠️ Unpredictable results │ │
│ │ ❌ Cannot be indexed │ │
│ │ ⚠️ Limited optimization │ │
│ │ ❌ Cannot cache results │ │
│ │ │ │
│ │ Examples: │ │
│ │ • Date/Time: NOW(), CURDATE(), GETDATE() │ │
│ │ • Random: RAND(), UUID(), NEWID() │ │
│ │ • User context: CURRENT_USER(), @@IDENTITY │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Impact on Indexing: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Deterministic Function: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ CREATE INDEX idx_year │ │ │
│ │ │ ON orders(YEAR(order_date)) │ │ │
│ │ │ │ │ │
│ │ │ ✅ Allowed (if YEAR is deterministic) │ │ │
│ │ │ ⚡ Fast lookups │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Non-Deterministic Function: │ │
│ │ ┌─────────────────────────────────────────────┐ │ │
│ │ │ CREATE INDEX idx_current │ │ │
│ │ │ ON orders(DATEDIFF(NOW(), order_date)) │ │ │
│ │ │ │ │ │
│ │ │ ❌ Not allowed (NOW() is non-deterministic) │ │ │
│ │ │ ⚠️ Would give inconsistent results │ │ │
│ │ └─────────────────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Decision Tree: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Does output depend on: │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Current date/time? │ │ │
│ │ └──┬──────────────────────────┬───┘ │ │
│ │ YES│ │NO │ │
│ │ ▼ ▼ │ │
│ │ Non-Det ┌─────────────┐ │ │
│ │ │ Random val? │ │ │
│ │ └──┬───────┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ Non-Det ┌──────────┐ │ │
│ │ │ User ctx?│ │ │
│ │ └──┬────┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ Non-Det Det │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Deterministic Functions
Functions that always return the same result for the same input parameters.
-- MySQL: Deterministic function
DELIMITER //
CREATE FUNCTION CalculateTax(
p_amount DECIMAL(10,2),
p_tax_rate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC -- Always same result for same inputs
BEGIN
RETURN p_amount * p_tax_rate;
END //
DELIMITER ;
Non-Deterministic Functions
Functions that may return different results for the same input parameters.
-- MySQL: Non-deterministic function
DELIMITER //
CREATE FUNCTION GetCurrentTimestamp()
RETURNS TIMESTAMP
NOT DETERMINISTIC -- Result changes over time
BEGIN
RETURN NOW();
END //
DELIMITER ;
-- Built-in non-deterministic functions:
-- NOW(), CURDATE(), RAND(), UUID()
Impact on Indexing
-- ❌ Cannot create index on non-deterministic function
CREATE INDEX idx_created_date
ON orders((YEAR(created_at))); -- Error if NOW() used
-- ✅ Can create index on deterministic function
CREATE INDEX idx_order_year
ON orders((YEAR(order_date))); -- OK if order_date is fixed
Best Practices
1. Use Appropriate Function Type
-- ✅ Good: Scalar for single value
CREATE FUNCTION GetCustomerName(@id INT)
RETURNS VARCHAR(100)
-- ✅ Good: Table-valued for multiple rows
CREATE FUNCTION GetCustomerOrders(@id INT)
RETURNS TABLE
-- ❌ Bad: Scalar returning concatenated string instead of table
CREATE FUNCTION GetAllOrders()
RETURNS VARCHAR(MAX) -- Returns comma-separated list
2. Mark Functions as DETERMINISTIC
-- ✅ Good: Explicitly mark deterministic functions
CREATE FUNCTION CalculateTotal(p_qty INT, p_price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN p_qty * p_price;
END;
-- Allows better optimization and indexing
3. Avoid Complex Logic in Functions
-- ❌ Bad: Complex logic in function
CREATE FUNCTION ComplexCalculation(...)
RETURNS DECIMAL(10,2)
BEGIN
-- 100 lines of complex logic
-- Multiple queries
-- Loops and cursors
END;
-- ✅ Good: Use stored procedure for complex logic
CREATE PROCEDURE ComplexCalculation(...)
BEGIN
-- Complex logic here
END;
4. Use Inline TVF Over Multi-Statement
-- ✅ Good: Inline TVF (better performance)
CREATE FUNCTION GetOrders(@customer_id INT)
RETURNS TABLE
AS
RETURN (
SELECT * FROM orders WHERE customer_id = @customer_id
);
-- ⚠️ Acceptable: Multi-statement only when necessary
CREATE FUNCTION GetOrdersWithLogic(@customer_id INT)
RETURNS @orders TABLE (...)
AS
BEGIN
-- Complex logic that requires multiple statements
RETURN;
END;
5. Handle NULL Values
-- ✅ Good: Handle NULL inputs
CREATE FUNCTION SafeDivide(
@numerator DECIMAL(10,2),
@denominator DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
IF @denominator IS NULL OR @denominator = 0
RETURN NULL;
RETURN @numerator / @denominator;
END;
6. Document Functions
-- ✅ Good: Well-documented function
/*
* Function: CalculateShippingCost
* Purpose: Calculate shipping cost based on weight and distance
* Parameters:
* @weight - Package weight in pounds
* @distance - Shipping distance in miles
* @is_express - Express shipping flag
* Returns: Shipping cost in USD
* Example: SELECT dbo.CalculateShippingCost(5.5, 100, 0)
*/
CREATE FUNCTION dbo.CalculateShippingCost(...)
RETURNS DECIMAL(10,2)
AS
BEGIN
-- Implementation
END;
7. Test Function Performance
-- Test function performance
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
-- Test scalar function
SELECT dbo.MyFunction(column) FROM large_table;
-- Compare with inline calculation
SELECT column * 1.1 FROM large_table;
-- Analyze execution plan
8. Avoid Functions in WHERE Clauses on Large Tables
-- ❌ Bad: Function prevents index usage
SELECT * FROM orders
WHERE YEAR(order_date) = 2023; -- Table scan
-- ✅ Good: Use sargable predicates
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01'; -- Index seek
Summary
This section covered:
- ✅ Scalar functions returning single values
- ✅ Table-valued functions (inline and multi-statement)
- ✅ Built-in functions (string, numeric, date, aggregate)
- ✅ User-defined functions for custom logic
- ✅ Deterministic vs non-deterministic functions
- ✅ Performance considerations
- ✅ Best practices for function development