Built-In vs User-Defined Functions in SQL
Introduction
SQL functions can be divided into two broad categories. Built-in functions are supplied, documented, and maintained by the database management system. User-defined functions, usually called UDFs, are created by developers or database teams to package application-specific calculations and reusable query logic.
-- Built-in functions
SELECT UPPER('sql') AS normalized_text,
ABS(-100) AS absolute_value,
ROUND(123.456, 2) AS rounded_value;
Built-in functions solve general problems such as changing text case, calculating totals, converting types, and working with dates. A UDF addresses a rule that belongs to a particular organization or application, such as calculating a contract discount, interpreting an internal status, or returning an authorized set of rows.
The main difference is not simply syntax. It is ownership. The database vendor owns a built-in function's implementation and compatibility contract. Your team owns a UDF's correctness, performance, security, tests, deployment, documentation, and future changes.
What Is a Built-In Function?
A built-in function is installed as part of the DBMS and can be called without creating it first. The engine recognizes its name, validates its arguments, determines a return type, and executes an implementation supplied by the vendor.
SELECT UPPER(employee_name) AS display_name,
ROUND(salary, 2) AS rounded_salary
FROM employee;
UPPER and ROUND are reusable across applications in the same product. Their documented behavior can still depend on data type, collation, precision, locale, time zone, or function options, so built-in does not mean identical across every database.
Common Built-In Function Categories
Database products normally provide built-in functions for text, numbers, dates, conversion, NULL handling, aggregation, analytics, JSON, XML, spatial data, security, and administration. The exact catalog varies by product and version.
| Category | Examples | Typical purpose |
|---|---|---|
| String | UPPER, LOWER, TRIM, SUBSTRING, REPLACE | Transform text |
| Numeric | ABS, ROUND, FLOOR, CEILING, POWER | Calculate numeric results |
| Date and time | CURRENT_DATE, EXTRACT, DATEADD | Inspect or calculate temporal values |
| Aggregate | COUNT, SUM, AVG, MIN, MAX | Summarize rows |
| Conversion | CAST and product-specific conversion functions | Change data types |
| NULL handling | COALESCE, NULLIF, NVL, IFNULL | Control missing-value behavior |
| Analytical | ROW_NUMBER, RANK, LAG, LEAD | Analyze ordered row sets |
Built-In String and Numeric Functions
String functions perform common character operations, while numeric functions calculate or reshape numeric values. They can be combined in one expression, but every transformation should have a clear data contract.
SELECT customer_id,
LOWER(TRIM(email)) AS normalized_email,
ROUND(ABS(account_balance), 2) AS balance_magnitude
FROM customer;
Although the syntax is concise, applying a function to an indexed column in WHERE can affect index use. The engine may need a matching expression index or a rewritten predicate to avoid evaluating every row.
Built-In Aggregate and Analytical Functions
Aggregate functions combine rows into one result per set or group. Analytical functions calculate across a window while preserving detail rows. These are built into the query engine and can participate in set-based plans.
SELECT department_id,
employee_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS department_average,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employee;
Reimplementing standard aggregation or ranking in a UDF usually makes the query harder for the optimizer to understand. Prefer the engine's set-based built-in features when they express the requirement directly.
Built-In Date, Conversion, and NULL Functions
Date functions work with current values, date parts, intervals, and temporal differences. Conversion functions change data types. NULL functions provide explicit fallback or comparison behavior.
SELECT order_id,
EXTRACT(YEAR FROM order_date) AS order_year,
CAST(total_amount AS DECIMAL(12, 2)) AS amount,
COALESCE(tracking_number, 'Not assigned') AS tracking_status
FROM orders;
These categories contain many product-specific differences. Date arithmetic, formatting, conversion failure behavior, return types, and NULL handling must be verified for the target DBMS.
Advantages of Built-In Functions
Built-in functions are immediately available, documented by the vendor, and widely understood by SQL developers. They are usually integrated with the optimizer, execution engine, type system, collation rules, vectorized execution, parallelism, and indexes more deeply than custom procedural code.
Vendor testing and support reduce the maintenance burden on application teams. Built-ins also make SQL more recognizable during review. These advantages do not remove the need to measure performance or test edge cases, but they make a built-in function the normal first choice.
Limitations of Built-In Functions
A built-in function solves a general problem and may not represent a company's exact business rule. Vendors also use different names and semantics, so portability is incomplete. A built-in formatting function may support one locale model while an application needs another.
Built-ins can still be expensive. Random sorting, regular expressions, JSON parsing, spatial calculations, functions on indexed predicate columns, and large window operations require deliberate query design.
What Is a User-Defined Function?
A user-defined function is a named database object created by a developer or administrator. It normally accepts zero or more parameters and returns a scalar value, a table, or another supported result according to the product.
-- Conceptual UDF call
SELECT order_id,
calculate_discount(customer_tier, order_total) AS discount_amount
FROM orders;
The function hides its implementation behind a stable name. That abstraction is useful only when the function has a clear contract, correct types, predictable NULL behavior, acceptable performance, and managed deployment.
Why Create a UDF?
A UDF can centralize a repeated domain calculation, improve consistency, make a query easier to read, and provide a controlled interface to complex logic. Good candidates include calculations that are truly shared and can be expressed without hidden side effects.
For example, if many reports use the same contract discount formula, a focused function may prevent slight variations across queries. The function should receive every meaningful input explicitly, including effective date or policy version when rules change over time.
Scalar User-Defined Functions
A scalar UDF returns one value for each evaluation. It may calculate tax, normalize an internal code, determine a category, or convert a domain-specific unit.
-- Product-specific creation syntax varies
CREATE FUNCTION calculate_tax(
amount DECIMAL(12, 2),
tax_rate DECIMAL(7, 4)
)
RETURNS DECIMAL(12, 2)
RETURN ROUND(amount * tax_rate, 2);
Called once, the function is straightforward. Called for millions of rows, its invocation and implementation cost can become significant. Some engines can inline suitable scalar UDFs; others execute them row by row or restrict optimization around them.
Table-Valued Functions
A table-valued function returns a relational result that can be queried in FROM, joined, filtered, or applied according to database syntax. It can provide a parameterized set such as active orders for one tenant or visible records for one user.
-- Conceptual usage
SELECT o.order_id, o.total_amount
FROM active_orders_for_customer(:customer_id) AS o
WHERE o.total_amount >= :minimum_amount;
Inline table-valued functions often expose their query to the optimizer more effectively than multi-statement functions that materialize intermediate rows. Cardinality estimates and pushdown behavior should be tested on the target platform.
UDF Parameters
Parameters define the visible inputs to a UDF. Names and data types should express business meaning and match calling columns without unnecessary implicit conversion. Multiple parameters can make a rule explicit, but a long parameter list may indicate that the function owns too many responsibilities.
SELECT calculate_shipping_cost(
package_weight,
destination_zone,
service_level
) AS shipping_cost
FROM shipment;
Avoid reading mutable configuration invisibly when the caller could pass a rate or policy version. Explicit inputs improve determinism, testing, replay, and auditability.
Function Return Types
A UDF's return type is part of its public contract. It controls precision, length, collation, client mapping, and downstream conversion. A decimal calculation needs enough precision for intermediate and final values, while a text result must not truncate valid data.
Changing a return type can break dependent views, procedures, reports, application mappings, or indexes. Treat return-type changes as interface changes and include them in compatibility review.
Deterministic and Non-Deterministic UDFs
A UDF is deterministic when the same explicit inputs produce the same output under fixed relevant semantics. A function that depends on current time, random state, session context, mutable tables, sequences, or external services is non-deterministic.
Some databases allow a developer to declare determinism or volatility. This declaration is a promise to the optimizer, not an automatic proof. An incorrect declaration can produce stale indexed values, unsafe rewrites, or replication problems.
Built-In vs User-Defined Functions
| Characteristic | Built-in function | User-defined function |
|---|---|---|
| Created by | Database vendor | Development or database team |
| Main purpose | General operations | Application-specific reusable logic |
| Maintenance | Vendor and upgrade process | Owning team |
| Optimizer integration | Usually strong | Varies by implementation and DBMS |
| Portability | Conceptually broad, syntax varies | Often requires product-specific rewrite |
| Testing responsibility | Verify usage and product behavior | Verify implementation, contract, and usage |
UDF vs Stored Procedure
A UDF is designed to return a value or table and participate in an expression or relational query. A stored procedure is normally called as an executable operation and is better suited to workflows, multiple statements, transaction coordination, output parameters, result sets, and controlled side effects.
| Concern | UDF | Stored procedure |
|---|---|---|
| Typical role | Calculation or reusable query expression | Workflow or operation |
| Used in SELECT/FROM | Often, depending on type | Normally no |
| Side effects | Usually restricted | Commonly supports controlled writes |
| Return | Scalar or table | Result sets, status, and output parameters |
Exact capabilities vary by product. Do not choose a UDF merely because it can hide more code; choose the database object that matches the behavior and transaction model.
When to Prefer a Built-In Function
Use a built-in when it already expresses the operation clearly. Text case conversion, trimming, aggregation, ranking, type conversion, date extraction, and NULL fallback rarely need a custom wrapper. Direct built-in use is easier to recognize and more likely to receive optimizer support.
A wrapper UDF that only calls one built-in adds another dependency and can hide useful information from the query plan. Create such a wrapper only when it enforces a meaningful contract that cannot be expressed clearly at the call site.
When to Consider a UDF
Consider a UDF when a focused business calculation is repeated across many queries, a parameterized row set has a stable meaning, or central ownership materially reduces inconsistency. The rule should be cohesive, well tested, and suitable for execution inside the database.
A good UDF has a narrow name, explicit inputs, defined output type, documented NULL and error behavior, no surprising side effects, and measured performance. Its benefits should exceed the added schema dependency.
When Not to Create a UDF
Do not create a UDF to duplicate a built-in, hide a single trivial expression, package a multi-step workflow, access an external service for every row, or avoid learning set-based SQL. Do not place rapidly changing application policy in a function unless the database is the agreed owner of that policy.
A function that secretly performs repeated table lookups can create an N+1 pattern inside one SQL statement. A join, common table expression, view, or inline table-valued function may expose the logic more clearly.
Scalar UDF Performance
Scalar UDF performance is a major design concern. A function called once may be inexpensive, while the same function called for ten million rows may dominate CPU and elapsed time. Procedural execution can also reduce parallelism or hide cardinality and cost from the optimizer.
Modern database versions may inline eligible functions, but eligibility depends on implementation details and configuration. Inspect the actual execution plan and runtime statistics rather than assuming inlining occurs.
Hidden SQL Inside UDFs
A concise outer query can hide substantial work inside a function. If a scalar UDF reads a table for every row, one visible scan can trigger thousands of internal lookups.
-- Looks simple, but calculate_customer_tier may query another table
SELECT order_id,
calculate_customer_tier(customer_id) AS customer_tier
FROM orders;
Prefer set-based joins when the lookup relationship can be expressed relationally. Review logical reads and function execution counts, not only the outer statement text.
Functions in WHERE Clauses
A built-in or UDF applied to an indexed column can prevent an ordinary index seek. This is not unique to custom functions, but a UDF can make the issue harder to notice.
-- Potentially non-sargable
WHERE normalize_code(product_code) = :normalized_code
-- Prefer a normalized indexed column when it is a durable business rule
WHERE normalized_product_code = :normalized_code
Expression indexes or generated columns can help when the database supports them and the expression satisfies determinism rules. Measure write cost and storage as well as read improvement.
Security and Permissions
A UDF is a database object with an owner, schema, execution rights, and dependencies. Grant only the permissions required. Schema qualification prevents name-resolution ambiguity and makes dependencies clearer.
Review dynamic SQL, definer-versus-caller execution context, row-level security, tenant boundaries, and data exposure. A function can leak information through returned values, timing, errors, or aggregated results even when direct table access is restricted.
Naming and Schema Qualification
Use names that describe the returned value or relation, such as billing.calculate_discount or reporting.active_customer_orders. Avoid generic names such as process_data or helper_function.
Always qualify custom functions with the intended schema in shared code. Schema qualification improves readability, avoids accidental resolution to another object, and can help plan stability and security review.
Versioning and Compatibility
A UDF is an interface used by queries, views, procedures, reports, jobs, and applications. Changing its parameters, return type, NULL behavior, precision, or meaning can break consumers even when the function name remains unchanged.
Prefer backward-compatible changes where possible. For a breaking change, introduce a versioned function, migrate callers, verify results, and retire the old version after dependency checks. Avoid embedding a version number when semantic versioning is not actually managed.
Source Control and Deployment
Store function definitions in version control with repeatable migration scripts, code review, tests, and rollback guidance. Environments should receive the same definition in a known order before dependent objects or application code are enabled.
Deployment automation should detect failed changes and validate ownership, permissions, dependencies, and database compatibility. Editing a function manually in production creates configuration drift and weakens incident recovery.
Dependency Management
Before altering or dropping a UDF, identify dependent views, computed columns, indexes, procedures, triggers, reports, jobs, and application queries. Database dependency catalogs help but may not capture dynamically generated SQL or external tools.
A controlled removal process deprecates the function, migrates callers, monitors remaining use, removes dependent objects in the correct order, and preserves a tested rollback path.
NULL Handling
A UDF contract should state whether NULL inputs return NULL, use a fallback, raise an error, or produce another defined result. Hidden fallback can make incomplete data appear valid, while unexpected propagation can remove rows from comparisons.
-- Call-site behavior is explicit
SELECT calculate_tax(
order_total,
COALESCE(tax_rate, 0)
) AS tax_amount
FROM orders;
Whether zero is a valid replacement depends on the business rule. Test all-NULL, partially NULL, empty, zero, and boundary inputs.
Error Handling and Side Effects
Define behavior for invalid input, overflow, division by zero, missing reference data, and out-of-range dates. Silent fallback may hide defects; unhandled errors may fail a complete statement. Choose intentionally and make errors observable.
Functions should generally avoid side effects. Logging, sending messages, changing tables, allocating sequence values, or calling external services makes evaluation order and retries difficult to reason about. Use procedures, jobs, or application services for workflows with effects.
Single Responsibility and Composition
A focused function should answer one clear question. Small cohesive functions are easier to test and reason about, but excessive chains can hide cost and create deep dependencies. Composition should improve clarity rather than turn a simple query into an invisible call graph.
SELECT ROUND(
calculate_discount(order_total, discount_rate),
2
) AS rounded_discount
FROM orders;
This combines a domain UDF with a built-in rounding function. Document where rounding belongs so callers do not apply inconsistent precision rules.
Testing User-Defined Functions
UDF tests should verify normal inputs, NULL, empty values, negative and zero values, maximum precision, invalid inputs, boundaries, data-type conversion, collation, time zone, and concurrency when relevant. Deterministic functions should return exact repeatable results.
Test set-based use as well as isolated calls. A function that passes ten unit cases may still be too slow in a million-row query. Compare actual plans, execution count, CPU, reads, elapsed time, memory, spills, and parallelism with realistic data distributions.
Built-In Function Portability
Standard functions such as COUNT, SUM, AVG, UPPER, LOWER, COALESCE, and CAST are broadly recognizable, but return types and edge behavior can differ. Date arithmetic, formatting, string concatenation, regular expressions, JSON, and geospatial functions vary more substantially.
When portability matters, isolate vendor-specific SQL, use compatibility tests, and avoid assuming that matching names guarantee matching semantics.
UDF Portability
UDF creation syntax, supported languages, volatility declarations, table-valued capabilities, security context, and transaction restrictions differ across products. Migrating a UDF often requires a redesign rather than a direct syntax translation.
Keep domain rules independent of vendor syntax where practical, document dependencies, and decide whether the database or application layer should own the function before committing to a platform-specific implementation.
SQL Server UDFs
SQL Server supports scalar functions and inline or multi-statement table-valued functions. Inline table-valued functions generally expose logic to the optimizer more effectively. Recent versions can inline some scalar UDFs, but eligibility and configuration matter.
Computed-column and indexed-view scenarios add determinism, precision, schema binding, ownership, data-type, and SET-option requirements. Verify the specific target version.
PostgreSQL Functions
PostgreSQL functions can use SQL, PL/pgSQL, and other trusted or supported languages. They declare return types and volatility categories such as IMMUTABLE, STABLE, and VOLATILE. Parallel-safety and security attributes also influence execution.
An inaccurate volatility declaration can permit unsafe optimizer behavior. Choose the strictest truthful category after reviewing hidden state and called functions.
MySQL Stored Functions
MySQL stored functions return a value and can declare characteristics such as DETERMINISTIC and data-access behavior. These declarations matter for understanding, binary logging, and replication, but the server may trust rather than prove them.
Test SQL modes, character sets, collations, privileges, and replication behavior in the deployment environment.
Oracle Functions
Oracle functions can be standalone or packaged and may be written in PL/SQL or supported external languages. Function-based indexes and deterministic declarations create strong performance options and equally strong correctness responsibilities.
Package state, session context, table reads, and current-time dependencies must be considered before a function is described as deterministic.
Real-World E-Commerce Example
An e-commerce system may use built-ins for trimming coupon codes, rounding prices, and aggregating order totals. A UDF can calculate a contract-specific discount when the rule is shared across reports and checkout reconciliation.
SELECT order_id,
ROUND(order_total, 2) AS displayed_total,
pricing.calculate_discount(
customer_tier,
order_total,
discount_policy_version
) AS discount_amount
FROM orders;
Passing the policy version makes historical orders reproducible. The UDF should not silently read today's active promotion when recalculating an old invoice.
Real-World Payroll Example
Payroll can use built-ins for date extraction, rounding, and aggregation. A UDF may calculate one well-defined allowance from explicit grade, location, and effective-date inputs. Statutory or frequently changing rules need versioning and expert review.
Payroll tests should cover thresholds, maximums, retroactive dates, NULL inputs, rounding, currency precision, and every supported policy version. Final amounts should reconcile with independent examples approved by the business owner.
Choosing the Correct Ownership Layer
Not every reusable calculation belongs in the database. A rule used primarily by one application may be easier to version, test, and deploy in application code. A data-integrity rule shared by many writers may belong in a constraint, generated column, or database function. A reporting transformation may belong in a view or semantic model.
Choose ownership based on where consistency must be enforced, which teams change the rule, how quickly releases occur, and which systems need the result. Avoid implementing the same rule independently in several layers without a declared source of truth.
Views, CTEs, and UDF Alternatives
A view can name a reusable relational query without accepting parameters. A common table expression can organize logic within one statement. A generated column can store or expose a stable row-derived value. A lookup table can model changing policy as data rather than code.
These alternatives often expose more relational structure to the optimizer than a procedural scalar UDF. Before creating a function, compare readability, parameter needs, dependency cost, optimization, security, and update frequency across all available designs.
Concurrency and Consistency
A UDF that reads tables can observe data according to the calling statement's transaction and isolation level. Concurrent updates may change results between calls, while snapshot rules may keep them stable inside one statement. This behavior must match the business contract.
For historical calculations, pass effective dates or version identifiers and store the chosen result where appropriate. Do not let an old invoice or payroll record silently change because a UDF now reads the latest rate table.
Monitoring UDF Usage
Production monitoring should identify frequently called functions, cumulative CPU, logical reads, elapsed time, errors, and plans that invoke the function. A small per-call cost becomes significant at high row counts or concurrency.
Track both direct calls and calls through views, procedures, generated columns, and reports. When a function changes, compare result reconciliation and runtime baselines before and after deployment. Monitoring helps distinguish a bad implementation from a valid function used at the wrong query grain.
Rollback and Recovery Planning
A function deployment can change results for every dependent query immediately. A safe migration should include the previous definition, dependency order, permission restoration, data compatibility checks, and a clear rollback trigger.
If a new function changes persisted or indexed values, rolling back code alone may be insufficient. Generated columns, expression indexes, cached reports, materialized results, and previously written business records may need rebuilding or reconciliation. Plan those recovery steps before release.
Common Mistakes
- Recreating functionality that a clear built-in already provides.
- Putting unrelated business rules into one large UDF.
- Ignoring row-by-row execution cost and hidden SQL.
- Assuming a UDF is the same as a stored procedure.
- Leaving NULL, error, precision, and collation behavior undefined.
- Hardcoding rates or policies that change over time.
- Declaring determinism without reviewing hidden dependencies.
- Changing parameters or return types without dependency analysis.
- Deploying functions manually outside version control.
- Assuming a UDF is faster because its call site is shorter.
Best-Practice Checklist
- Prefer an appropriate built-in function first.
- Create a UDF only for meaningful reusable logic.
- Keep the function focused and name its result clearly.
- Use explicit parameters and suitable return types.
- Define NULL, error, precision, collation, and time-zone behavior.
- Avoid side effects and hidden table access where possible.
- Measure performance in realistic set-based queries.
- Review determinism and security declarations carefully.
- Version definitions, dependencies, permissions, and migrations.
- Maintain correctness, portability, regression, and load tests.
Interview Questions and Answers
What is a built-in function? It is a function supplied and maintained by the DBMS, such as UPPER, ROUND, COUNT, or COALESCE.
What is a user-defined function? It is a database function created by a development or database team to return a reusable scalar or table result.
What is the main difference? The vendor owns a built-in function, while the application or database team owns a UDF's implementation and lifecycle.
Why use a UDF? To centralize a focused, repeated domain calculation or parameterized relational expression.
Can a UDF accept parameters? Yes, subject to product syntax and supported types.
Can a UDF return a table? Many databases support table-returning functions, though capabilities differ.
Can a UDF call built-in functions? Yes. A UDF commonly combines built-ins with domain logic.
Are UDFs always faster? No. Invocation overhead, hidden SQL, weak estimates, and limited optimization can make them slower.
Is a UDF the same as a stored procedure? No. A UDF returns a value or table for use in queries, while a procedure usually executes a workflow and may perform writes.
Which should be preferred? Prefer a clear built-in when it solves the problem; use a UDF when custom reusable logic provides measurable value.
Key Takeaway
Built-in functions are vendor-provided tools for common SQL operations. User-defined functions are team-owned database objects for reusable application-specific logic. Built-ins should be the default because they are familiar and deeply integrated with the engine, while UDFs should be introduced deliberately.
GOOD FUNCTION DESIGN = USE BUILT-INS WHERE POSSIBLE
+ CREATE UDFS FOR REAL DOMAIN VALUE
+ EXPLICIT INPUT AND OUTPUT CONTRACTS
+ SET-BASED, MEASURED PERFORMANCE
+ CONTROLLED SECURITY AND DEPLOYMENT
+ COMPLETE AUTOMATED TESTING
A successful UDF makes important logic clearer without hiding excessive work, changing meaning unexpectedly, or creating an unmanaged dependency.