What Are SQL Functions?
Introduction
A SQL function is a reusable operation that accepts zero or more input values, performs a calculation or transformation, and returns a result. Functions help transform text, perform mathematics, work with dates, handle NULL, summarize rows, convert types, and encapsulate database logic.
SELECT UPPER('hello') AS result;
The input is 'hello', the function is UPPER(), and the returned result is HELLO. A function call is an expression, so it can usually participate anywhere an expression of its return type is allowed. Exact names, signatures, and behavior vary by database system.
Basic Function Syntax
A function name is followed by parentheses containing its arguments:
FUNCTION(argument)
FUNCTION(argument1, argument2, ...)
For example, ROUND(123.4567, 2) receives a numeric value and the desired decimal scale, then returns 123.46. Some functions have optional arguments, and some constructs return environmental values without an explicit argument:
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
Some products use function syntax such as NOW(), while standard constructs may omit parentheses. Consult the target database documentation rather than assuming one form is portable.
Functions Do Not Usually Change Stored Data
Using a function in SELECT normally transforms the result, not the underlying table value:
SELECT employee_name,
UPPER(employee_name) AS display_name
FROM employee;
The stored name remains unchanged. To persist a transformation, it must be part of an INSERT, UPDATE, generated column, trigger, or other data-changing mechanism. Keeping presentation transformations in the result avoids unintended permanent changes, while durable normalization should be an explicit data-model decision.
Why SQL Functions Matter
Functions let applications perform data-oriented work close to the data. They produce consistent formatting, reusable calculations, grouped summaries, type-safe conversions, conditional values, and analytic results. They can reduce data transfer when the database returns only the final value needed by the caller.
Functions also require judgment. A function on millions of rows can consume substantial CPU. A function wrapped around an indexed search column can obstruct efficient access. A convenient vendor function can reduce portability. Good SQL uses functions where they clarify the required transformation and understands their effect on semantics and plans.
Major Function Categories
SQL functions are commonly grouped by purpose:
- String functions transform and inspect character data.
- Numeric functions perform arithmetic and mathematical operations.
- Date and time functions construct, extract, and calculate temporal values.
- Conversion functions change values from one type to another.
- NULL-handling functions choose alternatives or test missing values.
- Aggregate functions summarize sets of rows.
- Window functions calculate across related rows without collapsing them.
- User-defined functions encapsulate application-specific reusable logic.
Another important distinction is between scalar functions, which return one value per input row, and aggregate functions, which return one value for a group of rows.
Scalar and Single-Row Functions
A scalar function evaluates inputs for one row and returns one value for that row. If a query reads 100 employees and applies LOWER to each name, it normally produces 100 transformed values. String, numeric, date, conversion, and NULL-handling functions are often scalar.
SELECT employee_id,
LOWER(email) AS normalized_email,
ROUND(salary / 12, 2) AS monthly_salary
FROM employee;
Scalar does not mean cheap. A complex scalar function may run once per candidate row. Always consider how many rows reach the expression and whether the transformation belongs in filtering, projection, storage, or application presentation.
String Functions
String functions work with character values. Common operations include UPPER, LOWER, TRIM, SUBSTRING, REPLACE, CONCAT, and length measurement. Names and indexing conventions can differ across products.
SELECT UPPER(customer_name) AS upper_name,
TRIM(email) AS clean_email,
SUBSTRING(account_code FROM 1 FOR 3) AS prefix
FROM customer;
String functions are useful for display and controlled normalization. They are not a complete data-quality strategy. Case conversion, whitespace, Unicode normalization, collation, locale, and special characters can affect matching. Define the business meaning of equality before normalizing identifiers or names.
Numeric Functions
Numeric functions perform rounding, absolute value, flooring, ceiling, powers, roots, and other calculations:
SELECT ROUND(123.4567, 2),
ABS(-25),
FLOOR(9.8),
CEILING(9.2),
POWER(2, 8);
Input and return types matter. Integer division, decimal precision, floating-point approximation, overflow, and product-specific rounding rules can change results. Financial rounding must follow the business policy at the correct stage. Do not assume the default ROUND behavior automatically satisfies tax, currency, or regulatory requirements.
Date and Time Functions
Date functions return current values, extract components, add intervals, calculate differences, truncate periods, and convert time zones. They are among the least portable function families because databases use different names, interval syntax, precision, and time-zone types.
SELECT CURRENT_DATE,
CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM created_at)
FROM orders;
Current-time functions are non-deterministic relative to repeated execution. Use explicit transaction or application timestamps when several operations must share one business instant. Store and compare timestamps with a documented time-zone strategy, especially around daylight-saving transitions and historical offsets.
Aggregate Functions
Aggregate functions summarize multiple rows. COUNT counts, SUM totals numeric values, AVG computes an average, and MIN and MAX return boundaries.
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employee
GROUP BY department_id;
GROUP BY defines the population for each result. Without GROUP BY, an aggregate normally summarizes the entire filtered set. Filter source rows in WHERE and aggregate groups in HAVING. Security and tenant predicates must be applied before aggregation so one tenant's data cannot influence another tenant's totals.
Scalar versus Aggregate Functions
| Characteristic | Scalar function | Aggregate function |
|---|---|---|
| Input scope | One row or expression | A set or group of rows |
| Output | One value per input row | One value per group |
| Examples | UPPER, ROUND, CAST | COUNT, SUM, AVG |
| Row count | Usually preserved | Usually reduced to groups |
Scalar and aggregate functions can be combined. ROUND(AVG(salary), 2) first computes one average for the group and then rounds that aggregate result. The database's grouping rules determine which nested combinations are valid.
Functions in SQL Clauses
Functions can appear in SELECT expressions, WHERE predicates, JOIN conditions, GROUP BY, HAVING, ORDER BY, CASE, INSERT values, and UPDATE assignments. Their placement changes both meaning and cost.
SELECT customer_id,
ROUND(SUM(amount), 2) AS total_amount
FROM payment
WHERE paid_at >= :from_time
AND paid_at < :to_time
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC;
A function in SELECT runs for rows that survive filtering. A function in WHERE may be evaluated while identifying candidate rows and can affect index use. A function in GROUP BY determines grouping keys, and a function in ORDER BY may require a computed sort.
Sargability and Function Predicates
Wrapping an indexed column in a function can prevent direct index navigation:
-- Often non-sargable
WHERE DATE(created_at) = DATE '2026-11-07'
-- Index-friendly range
WHERE created_at >= TIMESTAMP '2026-11-07 00:00:00'
AND created_at < TIMESTAMP '2026-11-08 00:00:00'
Functions are not automatically slow. The question is where they run, how many rows they process, and whether a matching generated column or expression index exists. Keep filtering columns direct when possible, filter early according to semantics, and inspect the actual execution plan.
Functions in JOIN Conditions
Functions on join keys can require transformation of many rows and prevent normal key indexes from matching efficiently:
-- Potentially expensive
ON LOWER(a.external_code) = LOWER(b.external_code)
If case-insensitive identity is a durable requirement, consider compatible collations, normalized stored keys, generated columns, or expression indexes. Joining on typed stable identifiers is usually clearer and safer than joining on descriptions or formatted text.
Functions in INSERT, UPDATE, and Defaults
Functions can calculate values during data changes. An INSERT may capture a timestamp, normalize an input, or derive a value. An UPDATE can transform existing data:
INSERT INTO audit_event (event_type, recorded_at)
VALUES (:event_type, CURRENT_TIMESTAMP);
UPDATE customer
SET display_name = TRIM(display_name)
WHERE customer_id = :customer_id;
These statements persist the result, unlike a function used only in SELECT. Verify that repeated execution is safe and that normalization does not erase meaningful data. Large transformation updates should be previewed, batched where appropriate, and protected with backups, transactions, and affected-row checks.
Functions may also appear in column defaults, subject to product rules. A current timestamp is a common default for creation time, but the business must decide whether the database clock, application clock, transaction start, or statement time represents the event. Non-deterministic defaults simplify inserts while making reproducible tests and historical backfills more deliberate. Document which layer owns each generated value.
Nested Functions
The output of one function can become the input to another:
SELECT UPPER(TRIM(customer_name)) AS display_name
FROM customer;
Evaluation proceeds from the inner function outward for the logical expression: TRIM returns a value, then UPPER receives it. Deep nesting reduces readability and can duplicate expensive work. Use aliases, common table expressions, or clearly named generated values when intermediate concepts deserve visibility. Do not assume written expression order dictates physical execution beyond the required semantics.
NULL and Function Behavior
Many scalar functions propagate NULL: if a required input is NULL, the result is NULL. Behavior varies by function. CONCAT may ignore NULL, treat it as empty text, or return NULL depending on product and form. Always check the documented contract.
Aggregate functions generally ignore NULL inputs, except that COUNT(*) counts rows regardless of column values. COUNT(column) counts only non-NULL values. AVG ignores NULL rather than treating it as zero, so substituting zero changes the denominator and business meaning.
SELECT COUNT(*) AS all_rows,
COUNT(bonus) AS rows_with_bonus,
AVG(bonus) AS average_known_bonus
FROM employee;
Empty Sets and Aggregates
Aggregates have important empty-set behavior. COUNT returns zero when no rows match. SUM, AVG, MIN, and MAX normally return NULL because no value exists to summarize.
SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM payment
WHERE customer_id = :customer_id;
Use COALESCE only when the business output truly defines no matching payments as a total of zero. Preserving NULL can be more accurate when it means "not measured," "not applicable," or "unknown."
COALESCE and NULL Alternatives
COALESCE returns the first non-NULL expression from left to right:
SELECT COALESCE(preferred_name, legal_name, 'Unknown') AS display_name
FROM customer;
It is standard and usually more portable than vendor-specific alternatives. Its arguments should have compatible types, and the resulting type follows database resolution rules. COALESCE is a selection rule, not a universal command to replace every NULL. Document why each fallback is semantically valid.
Conversion Functions
CAST performs explicit type conversion:
SELECT CAST(order_id AS VARCHAR(30)) AS order_text,
CAST(:input_date AS DATE) AS parsed_date;
Explicit conversion communicates intent and is easier to review than relying on implicit type precedence. Conversion can fail for invalid values or lose precision. Avoid converting an indexed column inside a filter when the parameter can be converted once to the column's type. Formatting values for display should usually happen after filtering and aggregation.
Return Types and Function Signatures
A function signature describes its accepted argument types and return type. Products may overload one function name with several signatures. The selected overload can depend on input types, literals, and implicit conversions. That affects precision, collation, length, and plan choice.
Inspect the return type of expressions used in generated columns, indexes, joins, unions, and application mappings. An arithmetic expression that returns an approximate type can be inappropriate for currency, while a string function may return a length or collation different from the source.
Built-In and User-Defined Functions
Built-in functions are supplied and optimized by the database vendor. User-defined functions, or UDFs, encapsulate application-specific logic such as a reusable classification or calculation. UDF syntax and capabilities vary widely: some return one scalar value, others return a table.
A UDF can improve consistency and reduce duplication, but it can also hide expensive queries, inhibit optimization, execute row by row, or create dependency and deployment complexity. Keep functions focused, deterministic where appropriate, type-safe, tested, and documented. Do not move every business rule into a database function merely because reuse is possible.
Functions versus Stored Procedures
A function primarily returns a value and is often usable inside a query expression. A stored procedure represents an operation and may execute multiple statements, manage transactions, return several result sets, or perform data changes. Exact restrictions vary by database.
Choose based on the interface required. A reusable calculation belongs naturally in a function; a multi-step business operation with controlled side effects may belong in a procedure or application service. Do not assume one product's function/procedure boundary applies to another.
Deterministic and Non-Deterministic Functions
A deterministic function returns the same result for the same inputs under the defined environment. A non-deterministic function can vary, such as current timestamp, random value, sequence access, or a function depending on changing table data.
Determinism matters for generated columns, expression indexes, constraints, query rewrites, caching, replication, and test repeatability. Product rules may classify functions more strictly than an informal definition. Never use ordinary random functions for security tokens, and do not use current-time functions when a durable historical event needs an explicitly captured timestamp.
Generated Columns and Expression Indexes
Some databases can persist or virtually calculate a generated column and index it. Others support an index directly on an expression:
-- Product-specific example concept
CREATE INDEX ix_customer_normalized_email
ON customer (LOWER(TRIM(email)));
This can make a required transformation searchable, but the query expression must normally match the indexed definition. Determinism, collation, NULL handling, function versioning, storage, and write overhead all matter. Add expression indexes for important measured patterns, not every convenient function call.
Functions and Performance
Function cost depends on complexity and row count. A small calculation across 20 rows is different from a scalar UDF across 100 million rows. Functions can consume CPU, block sargability, increase sort or grouping work, and complicate cardinality estimation.
Filter the correct population before expensive projection when semantics permit. Avoid repeated calls to the same expensive expression; compute it once in a suitable query layer. Precompute stable transformations only when synchronization and write cost are understood. Verify actual plans, row counts, logical reads, CPU, elapsed time, and concurrency rather than labeling functions as universally fast or slow.
Conditional Aggregation
CASE inside an aggregate can calculate several related metrics in one grouped query:
SELECT tenant_id,
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) AS paid_orders,
SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) AS paid_amount
FROM orders
WHERE created_at >= :from_time
AND created_at < :to_time
GROUP BY tenant_id;
Confirm whether the ELSE should be zero or NULL because that choice affects aggregates. Keep mandatory authorization and date scope in WHERE so excluded rows never influence any metric.
Window Functions
Window functions calculate across related rows while preserving each row. Unlike GROUP BY aggregates, they do not collapse the result to one row per group.
SELECT employee_id,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS department_average,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS salary_rank
FROM employee;
Window partitions, ordering, and frames determine the result. Add deterministic tie-breakers where order matters. Window functions can require large sorts and memory, so filter irrelevant rows first and inspect plans for large workloads.
Portability Across Databases
Standard functions such as CAST, COALESCE, COUNT, SUM, AVG, MIN, and MAX are broadly available, but details still vary. String extraction, concatenation, date arithmetic, date truncation, current time, regular expressions, JSON, and random functions are especially product-specific.
If portability matters, define a supported SQL subset, isolate vendor-specific expressions, and run conformance tests on every database. Similar names do not guarantee identical NULL behavior, return type, collation, precision, indexing, or time-zone semantics. Official documentation for the target version is the source of truth.
Security Considerations
Functions do not make dynamically constructed SQL safe. Use bound parameters for values and allowlists for dynamic identifiers, sort directions, and function choices. A parameter can safely supply an input to a function:
WHERE LOWER(email) = LOWER(:email)
That statement may still need a better indexing design, but security and performance are separate questions. UDFs and procedures also need least-privilege permissions, controlled dynamic SQL, tenant-safe queries, and review of execution context. Aggregate output can expose sensitive information through small groups, so reporting systems may need suppression and authorization rules.
Aliases and Reusable Expressions
A descriptive alias gives a function result a stable meaning in the output:
SELECT customer_id,
ROUND(SUM(amount), 2) AS lifetime_paid_amount
FROM payment
WHERE status = 'SETTLED'
GROUP BY customer_id;
The alias is clearer to applications and report readers than a generated expression name. Whether that alias can be referenced in GROUP BY, HAVING, or another expression in the same SELECT varies by clause and database. Do not assume an alias prevents the database from evaluating a repeated expression; inspect the plan when the expression is expensive.
For complex logic, a common table expression or derived table can name an intermediate result and separate calculation stages. That improves readability, but a CTE is not automatically materialized or faster. Its performance depends on how the target optimizer expands or executes it.
Functions with DISTINCT
DISTINCT can apply to the input of an aggregate:
SELECT COUNT(DISTINCT customer_id) AS unique_customers,
SUM(DISTINCT discount_amount) AS distinct_discount_values
FROM orders;
COUNT(DISTINCT customer_id) has a common business interpretation: the number of different customers. SUM(DISTINCT discount_amount) is very different; it adds each different amount once rather than adding every discount. That is rarely the same as a business total. DISTINCT may also require sorting or hashing, so use it only when duplicate elimination is part of the requirement, not to hide an incorrect join.
Function Evaluation and Declarative SQL
Nested function syntax defines value dependencies, but SQL remains declarative. The optimizer can transform expressions, fold constants, move predicates, or reuse calculations while preserving semantics. Do not depend on a left-to-right short-circuit assumption to avoid a conversion or arithmetic error.
-- Do not rely only on predicate order for safety
WHERE text_value IS NOT NULL
AND CAST(text_value AS INTEGER) > 100
If invalid text can exist, use a safe conversion facility supported by the database, validate data at ingestion, or isolate valid values through a guaranteed mechanism. Similarly, use NULLIF(denominator, 0) or another documented safe expression rather than assuming a preceding condition always prevents division by zero.
Functions and Data Quality
Functions can standardize values at a boundary, but they do not replace constraints and correct types. Trimming every email at query time may conceal an ingestion problem and add permanent runtime cost. Storing dates as text and repeatedly parsing them weakens validation and indexing.
Choose a canonical representation for stable identifiers and validate it before storage. Use NOT NULL, CHECK, UNIQUE, foreign keys, generated columns, and application validation where appropriate. Query-time functions remain useful for genuine presentation, analysis, and compatibility needs, but recurring cleanup logic is evidence that the data model or write path deserves attention.
Real-World Customer Summary
The following report combines filtering, scalar functions, conditional aggregation, NULL handling, and explicit aliases:
SELECT c.customer_id,
UPPER(TRIM(c.customer_name)) AS display_name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS lifetime_amount,
COALESCE(
SUM(CASE WHEN o.status = 'PAID' THEN o.total_amount ELSE 0 END),
0
) AS paid_amount,
MAX(o.created_at) AS most_recent_order
FROM customer c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.tenant_id = c.tenant_id
WHERE c.tenant_id = :tenant_id
AND c.deleted_at IS NULL
GROUP BY c.customer_id, c.customer_name;
The LEFT JOIN retains customers without orders. COUNT uses the nullable child key so such customers receive zero orders rather than one joined row. SUM would return NULL without matching orders, so COALESCE converts that empty aggregate to a business-defined zero. Tenant and lifecycle scope are applied before aggregation.
Designing a User-Defined Function
Before creating a UDF, define its contract: accepted types, return type, NULL policy, errors, determinism, collation or time-zone assumptions, and expected cost. A function named calculate_net_amount should state whether it rounds, which currency it assumes, and whether a NULL discount means zero or unknown.
Keep data access visible. A scalar function that secretly queries several tables can turn a simple SELECT into repeated hidden work. Prefer pure calculations where practical, and consider an inline table-valued form or explicit join if it lets the optimizer see relational logic. Version and deploy functions with the schema, test backward compatibility, and review permissions separately from application code.
Function Selection Guide
| Requirement | Typical choice | Main review question |
|---|---|---|
| Transform each row | Scalar function | How many rows execute it? |
| Summarize a set | Aggregate function | What is the group and NULL policy? |
| Compare rows without collapsing them | Window function | What partition, order, and frame apply? |
| Choose a fallback | COALESCE or CASE | Is the fallback semantically valid? |
| Change data type | CAST or safe conversion | Can precision be lost or input fail? |
| Reuse domain calculation | UDF or shared query layer | Will logic and cost remain visible? |
Production Monitoring for Function-Heavy Queries
A function that is harmless today can become expensive as row counts and concurrency grow. Monitor query frequency, CPU, logical reads, elapsed time, spills, and estimated-versus-actual rows. Track database upgrades because optimizer treatment of scalar UDFs, expression indexes, and generated columns can change.
When a regression appears, capture the exact SQL, parameters, schema, plan, and data distribution. Compare a function-free or precomputed alternative only after confirming semantic equivalence. Changes to NULL handling, rounding, collation, or time-zone behavior can produce a faster but incorrect result. Performance work is complete only when output and operational behavior are both verified.
Testing SQL Functions
Test normal inputs, boundary values, NULL, empty strings, whitespace, Unicode, invalid conversion text, negative and very large numbers, precision limits, leap years, time zones, and daylight-saving transitions. Aggregate tests should cover empty sets, one row, duplicates, NULL values, grouping, and filtered populations.
For deterministic UDFs, use table-driven input/output cases. For non-deterministic behavior, test properties and controlled clocks rather than exact values. Performance tests need production-like row counts and distributions. Compare plans and metrics before and after introducing an expression index or rewrite.
Common SQL Function Mistakes
- Assuming function names and semantics are portable across databases.
- Wrapping indexed search columns in functions without an index strategy.
- Replacing every NULL with zero or empty text without business justification.
- Confusing COUNT(*) with COUNT(nullable_column).
- Assuming AVG treats NULL as zero.
- Formatting dates or numbers before filtering them.
- Nesting many functions until the expression becomes difficult to review.
- Using functions to hide inconsistent storage types or poor data quality.
- Using COUNT when only existence is required.
- Ignoring function return types, precision, collation, or determinism.
Practical Review Checklist
- Confirm the function's purpose, signature, input types, and return type.
- Define NULL and empty-set behavior explicitly.
- Check whether the function runs per row, per group, or per result.
- Keep indexed filter and join columns sargable where possible.
- Verify date, time-zone, numeric precision, and rounding requirements.
- Use aliases for complex results and avoid unreadable nesting.
- Review determinism before generated columns, indexes, or caching.
- Use parameters and least privilege for security.
- Test target database versions for portability-sensitive behavior.
- Measure execution plans and runtime impact with representative data.
Interview Questions and Answers
What is a SQL function? It is a reusable operation that accepts inputs, performs processing, and returns a value or table result.
What is the difference between scalar and aggregate functions? A scalar function returns a value per input row; an aggregate returns one value for a set or group.
What is COUNT(*) versus COUNT(column)? COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.
Does AVG treat NULL as zero? No. AVG normally ignores NULL values.
What does COALESCE do? It returns the first non-NULL expression in its argument list.
Why can a function hurt index use? A transformation on the indexed column can prevent direct navigation of the stored index values.
What is a deterministic function? It returns the same result for the same inputs under its defined context.
Can functions be nested? Yes, when the inner return type is compatible with the outer argument, but deep nesting can reduce clarity.
Are SQL functions portable? Some standard functions are broadly portable, but many names and semantics vary by DBMS.
Key Takeaway
SQL functions transform, calculate, summarize, convert, and analyze data. Their value comes from expressing a clear operation with known input, output, NULL, type, and grouping behavior.
SAFE FUNCTION USAGE = CORRECT FUNCTION
+ COMPATIBLE INPUT TYPES
+ EXPLICIT NULL SEMANTICS
+ VERIFIED RETURN TYPE
+ SARGABLE FILTERING
+ PORTABILITY AWARENESS
+ MEASURED PERFORMANCE
Use functions deliberately, keep business meaning visible, test edge cases and empty sets, and verify plans when functions participate in high-volume filters, joins, sorting, grouping, or reusable database logic.