Scalar Functions — SQL Functions | OneNoughtOne

0/278

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

2 / 5

Scalar Functions

The Workhorse of SQL Functions

Scalar functions represent the most common and frequently used type of user-defined function in SQL database systems. The term "scalar" comes from mathematics and refers to a quantity that has only magnitude—a single value, as opposed to a vector or matrix. In database terms, a scalar function takes zero or more input values and produces exactly one output value for each invocation. Every time you've used built-in functions like UPPER(), ROUND(), SUBSTRING(), or GETDATE(), you've used scalar functions. These functions transform input values into output values on a row-by-row basis. When you create your own scalar functions, you extend SQL's vocabulary with domain-specific computations that seamlessly integrate into queries. However, scalar functions come with critical performance considerations that every database developer must understand. Unlike built-in functions that are compiled into the database engine, user-defined scalar functions introduce function-call overhead that can devastate query performance at scale. This page equips you with the knowledge to harness scalar functions effectively while avoiding common pitfalls.

What You Will Learn

By the end of this page, you will master scalar function design patterns for various data types, understand the performance implications of scalar functions and techniques to mitigate them, learn advanced patterns including pure functions, caching strategies, and composition, and explore real-world examples across financial calculations, string processing, data validation, and more.

Scalar Function Fundamentals

At their core, scalar functions implement a simple contract: accept inputs, compute, return one value . This contract enables their use anywhere a single-valued expression is valid. Understanding this fundamental nature guides both design and usage. The anatomy of a scalar function invocation reveals its row-by-row nature. Consider a query selecting from 10,000 rows with a scalar function in the SELECT list—the function executes 10,000 times, once per row. This is fundamentally different from aggregate functions that process all rows and return one value for the entire set (or group).

scalar_vs_aggregate.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Understanding scalar vs aggregate function behavior
 
-- Sample data setup
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    sale_date DATE
);
 
-- Scalar function: computes line total for EACH row
CREATE FUNCTION dbo.LineTotal(@Qty INT, @Price DECIMAL(10,2))
RETURNS DECIMAL(12, 2)
AS
BEGIN
    RETURN @Qty * @Price;
END;
 
-- Scalar invocation: function runs once PER ROW
SELECT 
    sale_id,
    quantity,
    unit_price,
    dbo.LineTotal(quantity, unit_price) AS line_total  -- Called 10,000 times for 10,000 rows
FROM sales;
 
-- Contrast with aggregate: runs on ALL rows, returns ONE value
SELECT 
    SUM(quantity * unit_price) AS grand_total  -- Single computation across all rows
FROM sales;
 
-- Scalar in WHERE: still per-row evaluation
SELECT * 
FROM sales 
WHERE dbo.LineTotal(quantity, unit_price) > 100;  -- Called for every row being filtered

Scalar Function Characteristics

•Single-Value Return — Always returns exactly one value of the declared return type. NULL is a valid return value and counts as "one value."
•Row-Context Execution — When used in SELECT or WHERE, the function sees one row's worth of data at a time. It has no visibility into other rows.
•Expression Substitutability — Anywhere you can write a literal, column, or expression of a compatible type, you can invoke a scalar function.
•Parameter Binding — Arguments are evaluated before the function call. The function receives finalized values, not expressions.
•Side-Effect Limitations — Scalar functions typically cannot modify database state (INSERT, UPDATE, DELETE) in most database systems.

Designing Effective Scalar Functions

Well-designed scalar functions follow principles that ensure correctness, performance, and maintainability. Before writing a function, consider its purpose, inputs, outputs, and how it will be used in queries. The design checklist for every scalar function should address these questions: What is the single responsibility of this function? What inputs does it need, and what are their types? How should it handle NULL inputs? Is the function deterministic (same inputs \to same output)? Will this function be called on large result sets? Are there built-in functions that already do this?

scalar_design_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
-- Pattern 1: Pure Computation (No Data Access)
-- Best performance, most reusable, truly deterministic
 
CREATE FUNCTION dbo.CalculateCompoundInterest
(
    @Principal DECIMAL(18, 2),
    @AnnualRate DECIMAL(8, 6),  -- e.g., 0.05 for 5%
    @CompoundingsPerYear INT,
    @Years INT
)
RETURNS DECIMAL(18, 2)
WITH SCHEMABINDING  -- Enables use in indexed views
AS
BEGIN
    -- Validate inputs
    IF @Principal IS NULL OR @AnnualRate IS NULL 
       OR @CompoundingsPerYear IS NULL OR @Years IS NULL
        RETURN NULL;
    
    IF @CompoundingsPerYear <= 0 OR @Years < 0
        RETURN NULL;
    
    -- A = P(1 + r/n)^(nt)
    RETURN @Principal * POWER(
        1 + (@AnnualRate / @CompoundingsPerYear),
        @CompoundingsPerYear * @Years
    );
END;
 
-- Pattern 2: String Transformation
-- Convert text to standardized format
 
CREATE FUNCTION dbo.NormalizePhoneNumber(@RawPhone NVARCHAR(50))
RETURNS CHAR(10)
WITH SCHEMABINDING
AS
BEGIN
    IF @RawPhone IS NULL RETURN NULL;
    
    DECLARE @Digits NVARCHAR(50) = '';
    DECLARE @i INT = 1;
    DECLARE @Char NCHAR(1);
    
    -- Extract only digits
    WHILE @i <= LEN(@RawPhone)
    BEGIN
        SET @Char = SUBSTRING(@RawPhone, @i, 1);
        IF @Char LIKE '[0-9]'
            SET @Digits = @Digits + @Char;
        SET @i = @i + 1;
    END;
    
    -- Remove leading 1 for US numbers
    IF LEN(@Digits) = 11 AND LEFT(@Digits, 1) = '1'
        SET @Digits = SUBSTRING(@Digits, 2, 10);
    
    -- Validate 10-digit result
    IF LEN(@Digits) <> 10
        RETURN NULL;
    
    RETURN @Digits;
END;
 
-- Pattern 3: Business Rule Encapsulation
-- Centralize complex conditional logic
 
CREATE FUNCTION dbo.GetShippingRate
(
    @Weight DECIMAL(10, 2),    -- in pounds
    @Zone INT,                  -- shipping zone 1-8
    @ServiceType VARCHAR(20)    -- 'STANDARD', 'EXPRESS', 'OVERNIGHT'
)
RETURNS DECIMAL(8, 2)
AS
BEGIN
    IF @Weight IS NULL OR @Zone IS NULL OR @ServiceType IS NULL
        RETURN NULL;
    
    DECLARE @BaseRate DECIMAL(8, 2);
    DECLARE @WeightCharge DECIMAL(8, 2);
    DECLARE @ZoneMultiplier DECIMAL(4, 2);
    
    -- Base rate by service type
    SET @BaseRate = CASE @ServiceType
        WHEN 'STANDARD' THEN 5.99
        WHEN 'EXPRESS' THEN 12.99
        WHEN 'OVERNIGHT' THEN 24.99
        ELSE NULL
    END;
    
    IF @BaseRate IS NULL RETURN NULL;
    
    -- Weight charge (tiered)
    SET @WeightCharge = CASE
        WHEN @Weight <= 1 THEN 0
        WHEN @Weight <= 5 THEN (@Weight - 1) * 0.50
        WHEN @Weight <= 20 THEN 2.00 + (@Weight - 5) * 0.35
        ELSE 2.00 + 5.25 + (@Weight - 20) * 0.25
    END;
    
    -- Zone multiplier
    SET @ZoneMultiplier = CASE @Zone
        WHEN 1 THEN 1.00
        WHEN 2 THEN 1.05
        WHEN 3 THEN 1.10
        WHEN 4 THEN 1.20
        WHEN 5 THEN 1.35
        WHEN 6 THEN 1.50
        WHEN 7 THEN 1.70
        WHEN 8 THEN 2.00
        ELSE NULL
    END;
    
    IF @ZoneMultiplier IS NULL RETURN NULL;
    
    RETURN ROUND((@BaseRate + @WeightCharge) * @ZoneMultiplier, 2);
END;

Single Responsibility Principle

Each scalar function should do one thing well. If you find yourself writing a function that calculates a discount AND formats it as currency AND logs the calculation, split it into three functions. This improves testability, reusability, and performance (you avoid unnecessary work when only one result is needed).

Working with Different Data Types

Scalar functions work with all SQL data types, each with specific considerations. Understanding type-specific patterns helps you write robust functions that handle edge cases correctly. Numeric functions deal with precision, scale, overflow, and division by zero. String functions must consider NULL, empty strings, encoding, and collation. Date/time functions face timezone issues, boundary conditions, and varying calendar systems. Binary functions work with raw bytes and hashing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Numeric scalar functions: precision and edge cases
 
-- Safe percentage calculation with overflow protection
CREATE FUNCTION dbo.SafePercentage
(
    @Part DECIMAL(18, 4),
    @Whole DECIMAL(18, 4),
    @DecimalPlaces INT = 2
)
RETURNS DECIMAL(18, 4)
WITH SCHEMABINDING
AS
BEGIN
    -- Handle NULL
    IF @Part IS NULL OR @Whole IS NULL
        RETURN NULL;
    
    -- Handle division by zero
    IF @Whole = 0
        RETURN CASE 
            WHEN @Part = 0 THEN 0  -- 0/0 = 0% (by convention)
            ELSE NULL              -- n/0 = undefined
        END;
    
    -- Calculate and round
    RETURN ROUND((@Part / @Whole) * 100, @DecimalPlaces);
END;
 
-- Currency rounding with banker's rounding
CREATE FUNCTION dbo.RoundCurrency
(
    @Amount DECIMAL(18, 4),
    @CurrencyCode CHAR(3) = 'USD'
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    IF @Amount IS NULL RETURN NULL;
    
    -- Different currencies have different minor unit rules
    DECLARE @Decimals INT = CASE @CurrencyCode
        WHEN 'JPY' THEN 0   -- Japanese Yen has no minor unit
        WHEN 'KWD' THEN 3   -- Kuwaiti Dinar has 3 decimals
        WHEN 'BHD' THEN 3   -- Bahraini Dinar has 3 decimals
        ELSE 2              -- Most currencies use 2 decimals
    END;
    
    RETURN ROUND(@Amount, @Decimals);
END;
 
-- Statistical: Standard deviation helper
CREATE FUNCTION dbo.VarianceComponent(@Value DECIMAL(18,6), @Mean DECIMAL(18,6))
RETURNS DECIMAL(18, 6)
WITH SCHEMABINDING
AS
BEGIN
    IF @Value IS NULL OR @Mean IS NULL RETURN NULL;
    RETURN POWER(@Value - @Mean, 2);
END;

Performance Implications

This section covers the most critical aspect of scalar function development. The way user-defined scalar functions are executed in most database systems creates performance characteristics that differ dramatically from built-in functions and inline expressions. Understanding these implications is essential for writing functions that perform well at scale. Many database performance problems trace back to poorly considered scalar function usage.

The Row-by-Row Execution Problem

User-defined scalar functions in most database systems (especially SQL Server) are executed once per row, with full context-switch overhead for each invocation. A function that takes 1 millisecond, applied to 1 million rows, adds 16+ minutes to query execution. Built-in functions are compiled directly into the execution plan without this overhead.

performance_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Performance demonstration: Scalar UDF vs inline expression
 
-- Setup: 1 million row test table
CREATE TABLE perf_test (
    id INT PRIMARY KEY,
    value1 INT,
    value2 INT
);
 
-- Insert test data
INSERT INTO perf_test (id, value1, value2)
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY a.object_id),
    ABS(CHECKSUM(NEWID())) % 1000,
    ABS(CHECKSUM(NEWID())) % 1000
FROM sys.objects a CROSS JOIN sys.objects b;
 
-- Scalar function version
CREATE FUNCTION dbo.CalcRatio(@V1 INT, @V2 INT)
RETURNS DECIMAL(10, 4)
AS
BEGIN
    IF @V2 = 0 RETURN NULL;
    RETURN CAST(@V1 AS DECIMAL(10, 4)) / @V2;
END;
 
-- Test 1: Using scalar UDF (SLOW)
SET STATISTICS TIME ON;
SELECT AVG(dbo.CalcRatio(value1, value2)) AS avg_ratio
FROM perf_test;
-- Typical time: 15-30 seconds
 
-- Test 2: Using inline expression (FAST)
SET STATISTICS TIME ON;
SELECT AVG(CASE WHEN value2 = 0 THEN NULL 
           ELSE CAST(value1 AS DECIMAL(10,4)) / value2 END) AS avg_ratio
FROM perf_test;
-- Typical time: < 1 second
 
-- The difference can be 20x-100x depending on function complexity!

Scalar Function Performance Factors
Factor	Impact	Mitigation Strategy
Function call overhead	Each invocation has interpreter/context switch cost	Use inline expressions or inline TVFs instead
Row-by-row execution	Cannot parallelize scalar UDFs (before SQL Server 2019)	Use OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
Plan caching	Function body may not be optimized with query	SQL Server 2019+ inlines T-SQL scalar UDFs
No predicate pushdown	WHERE dbo.Func(col) = X cannot use indexes	Create computed column with index instead
Data access in function	Each row triggers separate query execution	Avoid SELECT statements in scalar functions

Mitigation strategies: Prefer inline expressions for simple calculations—the optimizer handles them efficiently. Use inline table-valued functions (ITVF) instead of scalar functions when possible—the optimizer can flatten them into the calling query. Consider computed columns for frequently-used scalar function results—optionally with indexes. Leverage SQL Server 2019+ scalar UDF inlining by meeting inlining requirements (no side effects, no iteration, no table variables). Cache function results in a separate table for expensive, deterministic calculations with limited input domains.

SCHEMABINDING and Determinism

Two related concepts— SCHEMABINDING and determinism —significantly impact what you can do with scalar functions. Understanding these concepts unlocks advanced features like computed columns, indexed views, and result caching.

SCHEMABINDING

•Binds function to referenced schema objects
•Prevents DROP or ALTER of dependent tables/views
•Required for use in indexed views
•Required for persisted computed columns
•Prevents two-part naming (must use dbo.Table)
•Adds deployment complexity but ensures integrity

Determinism Requirements

•Same inputs must produce same output
•Cannot reference GETDATE(), RAND(), NEWID()
•Cannot access external resources
•Cannot call non-deterministic functions
•Enables query optimizer caching
•Required for persisted computed columns

schemabinding_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Deterministic function with SCHEMABINDING
-- Can be used in indexed views and computed columns
 
CREATE FUNCTION dbo.NormalizeCustomerCode(@Code VARCHAR(20))
RETURNS VARCHAR(20)
WITH SCHEMABINDING  -- Required for indexed views
AS
BEGIN
    IF @Code IS NULL RETURN NULL;
    
    -- Remove all whitespace and convert to uppercase
    RETURN UPPER(REPLACE(REPLACE(REPLACE(@Code, ' ', ''), '-', ''), '.', ''));
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.NormalizeCustomerCode'), 'IsDeterministic');
-- Returns 1 if deterministic
 
-- Now we can use it in a computed column
ALTER TABLE customers
ADD normalized_code AS dbo.NormalizeCustomerCode(customer_code) PERSISTED;
 
-- And even index it!
CREATE INDEX ix_normalized_code ON customers(normalized_code);
 
-- NON-deterministic function example (CANNOT be schema-bound for computed columns)
CREATE FUNCTION dbo.GetCurrentAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    -- Uses GETDATE() - NOT deterministic
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
 
-- This CANNOT be persisted or indexed
-- ALTER TABLE employees ADD age AS dbo.GetCurrentAge(birth_date) PERSISTED;
-- Error: cannot be persisted because it is not deterministic

Design for Determinism

When designing functions, separate time-dependent logic from time-independent logic. Instead of GetCurrentAge(birth_date), create AgeAsOf(birth_date, as_of_date) that accepts the reference date as a parameter. The function becomes deterministic, and callers can pass GETDATE() when needed.

SQL Server 2019+ Scalar UDF Inlining

SQL Server 2019 introduced scalar UDF inlining, a game-changing feature that automatically transforms eligible scalar functions into inline expressions during query optimization. This eliminates the row-by-row execution overhead that plagued scalar functions for decades. With inlining, the query optimizer treats the function body as if you had written the expression directly in the query. This enables parallelism, predicate pushdown, and all other optimization techniques.

scalar_inlining.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Check if a function is inlineable
SELECT 
    OBJECT_NAME(object_id) AS function_name,
    is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.CalculateCompoundInterest');
 
-- To be inlineable, a function must:
-- 1. Be a T-SQL scalar UDF (not CLR)
-- 2. Not use table variables, cursors, or WHILE loops
-- 3. Not call non-inlineable functions
-- 4. Not reference large objects (MAX types in certain contexts)
-- 5. Not use certain constructs (THROW, RETURN without expression, etc.)
 
-- INLINEABLE function example
CREATE OR ALTER FUNCTION dbo.CalculateTax(@Amount DECIMAL(18,2), @Rate DECIMAL(5,4))
RETURNS DECIMAL(18,2)
WITH INLINE = ON  -- Explicitly enable (default for eligible functions)
AS
BEGIN
    RETURN @Amount * @Rate;
END;
GO
 
-- Check query plan - function should be inlined
SELECT product_id, price, dbo.CalculateTax(price, 0.08) AS tax
FROM products
OPTION (RECOMPILE);  -- Forces fresh plan to see inlining
 
-- NON-INLINEABLE function (uses WHILE loop)
CREATE FUNCTION dbo.FactorialLoop(@N INT)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Result BIGINT = 1;
    WHILE @N > 1
    BEGIN
        SET @Result = @Result * @N;
        SET @N = @N - 1;
    END;
    RETURN @Result;
END;
-- is_inlineable = 0
 
-- Force disable inlining for specific function
CREATE OR ALTER FUNCTION dbo.MyFunction(@X INT)
RETURNS INT
WITH INLINE = OFF  -- Disable inlining
AS
BEGIN
    RETURN @X * 2;
END;
 
-- Database-level control
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;  -- Disable globally

Inlining Requirements Summary

•Database compatibility level 150+ — Must be on SQL Server 2019 compatibility
•No EXECUTE AS clause — Functions with explicit execution context cannot be inlined
•No loops or cursors — WHILE, cursor operations block inlining
•No table variables — DECLARE @T TABLE blocks inlining (table types OK)
•No TRY/CATCH — Exception handling prevents inlining
•Limited built-in restrictions — Some functions like NEWID() block inlining

Write for Inlining

When targeting SQL Server 2019+, design functions with inlining in mind. Avoid constructs that block inlining when alternatives exist. The performance difference between inlined and non-inlined functions can be 10-100x. Check sys.sql_modules.is_inlineable to verify eligibility.

PostgreSQL Perspectives

PostgreSQL takes a different approach to scalar functions with its volatility categories and function inlining for SQL-language functions . Understanding these mechanics helps you write efficient functions in PostgreSQL environments.

postgresql_scalar_functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- PostgreSQL volatility categories
 
-- IMMUTABLE: Always returns same result for same inputs
-- Best for optimizer, can be used in index expressions
CREATE OR REPLACE FUNCTION calculate_area(radius NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT PI() * radius * radius;
$$;
 
-- Can be used in index expressions!
CREATE INDEX idx_area ON circles (calculate_area(radius));
 
-- STABLE: Same result within a transaction
-- Cannot be used in indexes but optimizer can cache within query
CREATE OR REPLACE FUNCTION get_current_user_timezone()
RETURNS TEXT
LANGUAGE SQL
STABLE PARALLEL SAFE
AS $$
    SELECT current_setting('TIMEZONE');
$$;
 
-- VOLATILE: May return different results on each call
-- Default if not specified; optimizer cannot cache
CREATE OR REPLACE FUNCTION log_access_and_return(val INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    INSERT INTO access_log (accessed_at, value) VALUES (NOW(), val);
    RETURN val;
END;
$$;
 
-- SQL-language functions can be INLINED by PostgreSQL
-- This is like SQL Server's scalar UDF inlining but more mature
 
-- INLINABLE (simple SQL function)
CREATE OR REPLACE FUNCTION double_value(x INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT x * 2;
$$;
 
-- CHECK IF INLINE HAPPENED: EXPLAIN shows expression, not function call
EXPLAIN SELECT double_value(id) FROM test_table;
-- Should show: "id * 2" not "double_value(id)"
 
-- NOT INLINABLE (PL/pgSQL procedural function)
CREATE OR REPLACE FUNCTION double_value_plpgsql(x INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN x * 2;
END;
$$;
 
-- Performance TIP: Prefer LANGUAGE SQL over LANGUAGE plpgsql
-- for simple functions to enable inlining

PostgreSQL Function Inlining Rules

PostgreSQL can inline SQL-language functions (not PL/pgSQL) under certain conditions: function must be marked IMMUTABLE or STABLE, must contain a single SQL statement, and must not have security-related constraints. For maximum performance, prefer simple SQL-language functions with proper volatility declarations.

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

2 / 5

Scalar Functions

The Workhorse of SQL Functions

Scalar functions represent the most common and frequently used type of user-defined function in SQL database systems. The term "scalar" comes from mathematics and refers to a quantity that has only magnitude—a single value, as opposed to a vector or matrix. In database terms, a scalar function takes zero or more input values and produces exactly one output value for each invocation. Every time you've used built-in functions like UPPER(), ROUND(), SUBSTRING(), or GETDATE(), you've used scalar functions. These functions transform input values into output values on a row-by-row basis. When you create your own scalar functions, you extend SQL's vocabulary with domain-specific computations that seamlessly integrate into queries. However, scalar functions come with critical performance considerations that every database developer must understand. Unlike built-in functions that are compiled into the database engine, user-defined scalar functions introduce function-call overhead that can devastate query performance at scale. This page equips you with the knowledge to harness scalar functions effectively while avoiding common pitfalls.

What You Will Learn

By the end of this page, you will master scalar function design patterns for various data types, understand the performance implications of scalar functions and techniques to mitigate them, learn advanced patterns including pure functions, caching strategies, and composition, and explore real-world examples across financial calculations, string processing, data validation, and more.

Scalar Function Fundamentals

At their core, scalar functions implement a simple contract: accept inputs, compute, return one value . This contract enables their use anywhere a single-valued expression is valid. Understanding this fundamental nature guides both design and usage. The anatomy of a scalar function invocation reveals its row-by-row nature. Consider a query selecting from 10,000 rows with a scalar function in the SELECT list—the function executes 10,000 times, once per row. This is fundamentally different from aggregate functions that process all rows and return one value for the entire set (or group).

scalar_vs_aggregate.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Understanding scalar vs aggregate function behavior
 
-- Sample data setup
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    sale_date DATE
);
 
-- Scalar function: computes line total for EACH row
CREATE FUNCTION dbo.LineTotal(@Qty INT, @Price DECIMAL(10,2))
RETURNS DECIMAL(12, 2)
AS
BEGIN
    RETURN @Qty * @Price;
END;
 
-- Scalar invocation: function runs once PER ROW
SELECT 
    sale_id,
    quantity,
    unit_price,
    dbo.LineTotal(quantity, unit_price) AS line_total  -- Called 10,000 times for 10,000 rows
FROM sales;
 
-- Contrast with aggregate: runs on ALL rows, returns ONE value
SELECT 
    SUM(quantity * unit_price) AS grand_total  -- Single computation across all rows
FROM sales;
 
-- Scalar in WHERE: still per-row evaluation
SELECT * 
FROM sales 
WHERE dbo.LineTotal(quantity, unit_price) > 100;  -- Called for every row being filtered

Scalar Function Characteristics

•Single-Value Return — Always returns exactly one value of the declared return type. NULL is a valid return value and counts as "one value."
•Row-Context Execution — When used in SELECT or WHERE, the function sees one row's worth of data at a time. It has no visibility into other rows.
•Expression Substitutability — Anywhere you can write a literal, column, or expression of a compatible type, you can invoke a scalar function.
•Parameter Binding — Arguments are evaluated before the function call. The function receives finalized values, not expressions.
•Side-Effect Limitations — Scalar functions typically cannot modify database state (INSERT, UPDATE, DELETE) in most database systems.

Designing Effective Scalar Functions

Well-designed scalar functions follow principles that ensure correctness, performance, and maintainability. Before writing a function, consider its purpose, inputs, outputs, and how it will be used in queries. The design checklist for every scalar function should address these questions: What is the single responsibility of this function? What inputs does it need, and what are their types? How should it handle NULL inputs? Is the function deterministic (same inputs \to same output)? Will this function be called on large result sets? Are there built-in functions that already do this?

scalar_design_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
-- Pattern 1: Pure Computation (No Data Access)
-- Best performance, most reusable, truly deterministic
 
CREATE FUNCTION dbo.CalculateCompoundInterest
(
    @Principal DECIMAL(18, 2),
    @AnnualRate DECIMAL(8, 6),  -- e.g., 0.05 for 5%
    @CompoundingsPerYear INT,
    @Years INT
)
RETURNS DECIMAL(18, 2)
WITH SCHEMABINDING  -- Enables use in indexed views
AS
BEGIN
    -- Validate inputs
    IF @Principal IS NULL OR @AnnualRate IS NULL 
       OR @CompoundingsPerYear IS NULL OR @Years IS NULL
        RETURN NULL;
    
    IF @CompoundingsPerYear <= 0 OR @Years < 0
        RETURN NULL;
    
    -- A = P(1 + r/n)^(nt)
    RETURN @Principal * POWER(
        1 + (@AnnualRate / @CompoundingsPerYear),
        @CompoundingsPerYear * @Years
    );
END;
 
-- Pattern 2: String Transformation
-- Convert text to standardized format
 
CREATE FUNCTION dbo.NormalizePhoneNumber(@RawPhone NVARCHAR(50))
RETURNS CHAR(10)
WITH SCHEMABINDING
AS
BEGIN
    IF @RawPhone IS NULL RETURN NULL;
    
    DECLARE @Digits NVARCHAR(50) = '';
    DECLARE @i INT = 1;
    DECLARE @Char NCHAR(1);
    
    -- Extract only digits
    WHILE @i <= LEN(@RawPhone)
    BEGIN
        SET @Char = SUBSTRING(@RawPhone, @i, 1);
        IF @Char LIKE '[0-9]'
            SET @Digits = @Digits + @Char;
        SET @i = @i + 1;
    END;
    
    -- Remove leading 1 for US numbers
    IF LEN(@Digits) = 11 AND LEFT(@Digits, 1) = '1'
        SET @Digits = SUBSTRING(@Digits, 2, 10);
    
    -- Validate 10-digit result
    IF LEN(@Digits) <> 10
        RETURN NULL;
    
    RETURN @Digits;
END;
 
-- Pattern 3: Business Rule Encapsulation
-- Centralize complex conditional logic
 
CREATE FUNCTION dbo.GetShippingRate
(
    @Weight DECIMAL(10, 2),    -- in pounds
    @Zone INT,                  -- shipping zone 1-8
    @ServiceType VARCHAR(20)    -- 'STANDARD', 'EXPRESS', 'OVERNIGHT'
)
RETURNS DECIMAL(8, 2)
AS
BEGIN
    IF @Weight IS NULL OR @Zone IS NULL OR @ServiceType IS NULL
        RETURN NULL;
    
    DECLARE @BaseRate DECIMAL(8, 2);
    DECLARE @WeightCharge DECIMAL(8, 2);
    DECLARE @ZoneMultiplier DECIMAL(4, 2);
    
    -- Base rate by service type
    SET @BaseRate = CASE @ServiceType
        WHEN 'STANDARD' THEN 5.99
        WHEN 'EXPRESS' THEN 12.99
        WHEN 'OVERNIGHT' THEN 24.99
        ELSE NULL
    END;
    
    IF @BaseRate IS NULL RETURN NULL;
    
    -- Weight charge (tiered)
    SET @WeightCharge = CASE
        WHEN @Weight <= 1 THEN 0
        WHEN @Weight <= 5 THEN (@Weight - 1) * 0.50
        WHEN @Weight <= 20 THEN 2.00 + (@Weight - 5) * 0.35
        ELSE 2.00 + 5.25 + (@Weight - 20) * 0.25
    END;
    
    -- Zone multiplier
    SET @ZoneMultiplier = CASE @Zone
        WHEN 1 THEN 1.00
        WHEN 2 THEN 1.05
        WHEN 3 THEN 1.10
        WHEN 4 THEN 1.20
        WHEN 5 THEN 1.35
        WHEN 6 THEN 1.50
        WHEN 7 THEN 1.70
        WHEN 8 THEN 2.00
        ELSE NULL
    END;
    
    IF @ZoneMultiplier IS NULL RETURN NULL;
    
    RETURN ROUND((@BaseRate + @WeightCharge) * @ZoneMultiplier, 2);
END;

Single Responsibility Principle

Each scalar function should do one thing well. If you find yourself writing a function that calculates a discount AND formats it as currency AND logs the calculation, split it into three functions. This improves testability, reusability, and performance (you avoid unnecessary work when only one result is needed).

Working with Different Data Types

Scalar functions work with all SQL data types, each with specific considerations. Understanding type-specific patterns helps you write robust functions that handle edge cases correctly. Numeric functions deal with precision, scale, overflow, and division by zero. String functions must consider NULL, empty strings, encoding, and collation. Date/time functions face timezone issues, boundary conditions, and varying calendar systems. Binary functions work with raw bytes and hashing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Numeric scalar functions: precision and edge cases
 
-- Safe percentage calculation with overflow protection
CREATE FUNCTION dbo.SafePercentage
(
    @Part DECIMAL(18, 4),
    @Whole DECIMAL(18, 4),
    @DecimalPlaces INT = 2
)
RETURNS DECIMAL(18, 4)
WITH SCHEMABINDING
AS
BEGIN
    -- Handle NULL
    IF @Part IS NULL OR @Whole IS NULL
        RETURN NULL;
    
    -- Handle division by zero
    IF @Whole = 0
        RETURN CASE 
            WHEN @Part = 0 THEN 0  -- 0/0 = 0% (by convention)
            ELSE NULL              -- n/0 = undefined
        END;
    
    -- Calculate and round
    RETURN ROUND((@Part / @Whole) * 100, @DecimalPlaces);
END;
 
-- Currency rounding with banker's rounding
CREATE FUNCTION dbo.RoundCurrency
(
    @Amount DECIMAL(18, 4),
    @CurrencyCode CHAR(3) = 'USD'
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    IF @Amount IS NULL RETURN NULL;
    
    -- Different currencies have different minor unit rules
    DECLARE @Decimals INT = CASE @CurrencyCode
        WHEN 'JPY' THEN 0   -- Japanese Yen has no minor unit
        WHEN 'KWD' THEN 3   -- Kuwaiti Dinar has 3 decimals
        WHEN 'BHD' THEN 3   -- Bahraini Dinar has 3 decimals
        ELSE 2              -- Most currencies use 2 decimals
    END;
    
    RETURN ROUND(@Amount, @Decimals);
END;
 
-- Statistical: Standard deviation helper
CREATE FUNCTION dbo.VarianceComponent(@Value DECIMAL(18,6), @Mean DECIMAL(18,6))
RETURNS DECIMAL(18, 6)
WITH SCHEMABINDING
AS
BEGIN
    IF @Value IS NULL OR @Mean IS NULL RETURN NULL;
    RETURN POWER(@Value - @Mean, 2);
END;

Performance Implications

This section covers the most critical aspect of scalar function development. The way user-defined scalar functions are executed in most database systems creates performance characteristics that differ dramatically from built-in functions and inline expressions. Understanding these implications is essential for writing functions that perform well at scale. Many database performance problems trace back to poorly considered scalar function usage.

The Row-by-Row Execution Problem

User-defined scalar functions in most database systems (especially SQL Server) are executed once per row, with full context-switch overhead for each invocation. A function that takes 1 millisecond, applied to 1 million rows, adds 16+ minutes to query execution. Built-in functions are compiled directly into the execution plan without this overhead.

performance_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Performance demonstration: Scalar UDF vs inline expression
 
-- Setup: 1 million row test table
CREATE TABLE perf_test (
    id INT PRIMARY KEY,
    value1 INT,
    value2 INT
);
 
-- Insert test data
INSERT INTO perf_test (id, value1, value2)
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY a.object_id),
    ABS(CHECKSUM(NEWID())) % 1000,
    ABS(CHECKSUM(NEWID())) % 1000
FROM sys.objects a CROSS JOIN sys.objects b;
 
-- Scalar function version
CREATE FUNCTION dbo.CalcRatio(@V1 INT, @V2 INT)
RETURNS DECIMAL(10, 4)
AS
BEGIN
    IF @V2 = 0 RETURN NULL;
    RETURN CAST(@V1 AS DECIMAL(10, 4)) / @V2;
END;
 
-- Test 1: Using scalar UDF (SLOW)
SET STATISTICS TIME ON;
SELECT AVG(dbo.CalcRatio(value1, value2)) AS avg_ratio
FROM perf_test;
-- Typical time: 15-30 seconds
 
-- Test 2: Using inline expression (FAST)
SET STATISTICS TIME ON;
SELECT AVG(CASE WHEN value2 = 0 THEN NULL 
           ELSE CAST(value1 AS DECIMAL(10,4)) / value2 END) AS avg_ratio
FROM perf_test;
-- Typical time: < 1 second
 
-- The difference can be 20x-100x depending on function complexity!

Scalar Function Performance Factors
Factor	Impact	Mitigation Strategy
Function call overhead	Each invocation has interpreter/context switch cost	Use inline expressions or inline TVFs instead
Row-by-row execution	Cannot parallelize scalar UDFs (before SQL Server 2019)	Use OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
Plan caching	Function body may not be optimized with query	SQL Server 2019+ inlines T-SQL scalar UDFs
No predicate pushdown	WHERE dbo.Func(col) = X cannot use indexes	Create computed column with index instead
Data access in function	Each row triggers separate query execution	Avoid SELECT statements in scalar functions

Mitigation strategies: Prefer inline expressions for simple calculations—the optimizer handles them efficiently. Use inline table-valued functions (ITVF) instead of scalar functions when possible—the optimizer can flatten them into the calling query. Consider computed columns for frequently-used scalar function results—optionally with indexes. Leverage SQL Server 2019+ scalar UDF inlining by meeting inlining requirements (no side effects, no iteration, no table variables). Cache function results in a separate table for expensive, deterministic calculations with limited input domains.

SCHEMABINDING and Determinism

Two related concepts— SCHEMABINDING and determinism —significantly impact what you can do with scalar functions. Understanding these concepts unlocks advanced features like computed columns, indexed views, and result caching.

SCHEMABINDING

•Binds function to referenced schema objects
•Prevents DROP or ALTER of dependent tables/views
•Required for use in indexed views
•Required for persisted computed columns
•Prevents two-part naming (must use dbo.Table)
•Adds deployment complexity but ensures integrity

Determinism Requirements

•Same inputs must produce same output
•Cannot reference GETDATE(), RAND(), NEWID()
•Cannot access external resources
•Cannot call non-deterministic functions
•Enables query optimizer caching
•Required for persisted computed columns

schemabinding_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Deterministic function with SCHEMABINDING
-- Can be used in indexed views and computed columns
 
CREATE FUNCTION dbo.NormalizeCustomerCode(@Code VARCHAR(20))
RETURNS VARCHAR(20)
WITH SCHEMABINDING  -- Required for indexed views
AS
BEGIN
    IF @Code IS NULL RETURN NULL;
    
    -- Remove all whitespace and convert to uppercase
    RETURN UPPER(REPLACE(REPLACE(REPLACE(@Code, ' ', ''), '-', ''), '.', ''));
END;
GO
 
-- Verify determinism
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.NormalizeCustomerCode'), 'IsDeterministic');
-- Returns 1 if deterministic
 
-- Now we can use it in a computed column
ALTER TABLE customers
ADD normalized_code AS dbo.NormalizeCustomerCode(customer_code) PERSISTED;
 
-- And even index it!
CREATE INDEX ix_normalized_code ON customers(normalized_code);
 
-- NON-deterministic function example (CANNOT be schema-bound for computed columns)
CREATE FUNCTION dbo.GetCurrentAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    -- Uses GETDATE() - NOT deterministic
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                  THEN 1 ELSE 0 END;
END;
 
-- This CANNOT be persisted or indexed
-- ALTER TABLE employees ADD age AS dbo.GetCurrentAge(birth_date) PERSISTED;
-- Error: cannot be persisted because it is not deterministic

Design for Determinism

When designing functions, separate time-dependent logic from time-independent logic. Instead of GetCurrentAge(birth_date), create AgeAsOf(birth_date, as_of_date) that accepts the reference date as a parameter. The function becomes deterministic, and callers can pass GETDATE() when needed.

SQL Server 2019+ Scalar UDF Inlining

SQL Server 2019 introduced scalar UDF inlining, a game-changing feature that automatically transforms eligible scalar functions into inline expressions during query optimization. This eliminates the row-by-row execution overhead that plagued scalar functions for decades. With inlining, the query optimizer treats the function body as if you had written the expression directly in the query. This enables parallelism, predicate pushdown, and all other optimization techniques.

scalar_inlining.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Check if a function is inlineable
SELECT 
    OBJECT_NAME(object_id) AS function_name,
    is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.CalculateCompoundInterest');
 
-- To be inlineable, a function must:
-- 1. Be a T-SQL scalar UDF (not CLR)
-- 2. Not use table variables, cursors, or WHILE loops
-- 3. Not call non-inlineable functions
-- 4. Not reference large objects (MAX types in certain contexts)
-- 5. Not use certain constructs (THROW, RETURN without expression, etc.)
 
-- INLINEABLE function example
CREATE OR ALTER FUNCTION dbo.CalculateTax(@Amount DECIMAL(18,2), @Rate DECIMAL(5,4))
RETURNS DECIMAL(18,2)
WITH INLINE = ON  -- Explicitly enable (default for eligible functions)
AS
BEGIN
    RETURN @Amount * @Rate;
END;
GO
 
-- Check query plan - function should be inlined
SELECT product_id, price, dbo.CalculateTax(price, 0.08) AS tax
FROM products
OPTION (RECOMPILE);  -- Forces fresh plan to see inlining
 
-- NON-INLINEABLE function (uses WHILE loop)
CREATE FUNCTION dbo.FactorialLoop(@N INT)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Result BIGINT = 1;
    WHILE @N > 1
    BEGIN
        SET @Result = @Result * @N;
        SET @N = @N - 1;
    END;
    RETURN @Result;
END;
-- is_inlineable = 0
 
-- Force disable inlining for specific function
CREATE OR ALTER FUNCTION dbo.MyFunction(@X INT)
RETURNS INT
WITH INLINE = OFF  -- Disable inlining
AS
BEGIN
    RETURN @X * 2;
END;
 
-- Database-level control
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;  -- Disable globally

Inlining Requirements Summary

•Database compatibility level 150+ — Must be on SQL Server 2019 compatibility
•No EXECUTE AS clause — Functions with explicit execution context cannot be inlined
•No loops or cursors — WHILE, cursor operations block inlining
•No table variables — DECLARE @T TABLE blocks inlining (table types OK)
•No TRY/CATCH — Exception handling prevents inlining
•Limited built-in restrictions — Some functions like NEWID() block inlining

Write for Inlining

When targeting SQL Server 2019+, design functions with inlining in mind. Avoid constructs that block inlining when alternatives exist. The performance difference between inlined and non-inlined functions can be 10-100x. Check sys.sql_modules.is_inlineable to verify eligibility.

PostgreSQL Perspectives

PostgreSQL takes a different approach to scalar functions with its volatility categories and function inlining for SQL-language functions . Understanding these mechanics helps you write efficient functions in PostgreSQL environments.

postgresql_scalar_functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- PostgreSQL volatility categories
 
-- IMMUTABLE: Always returns same result for same inputs
-- Best for optimizer, can be used in index expressions
CREATE OR REPLACE FUNCTION calculate_area(radius NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT PI() * radius * radius;
$$;
 
-- Can be used in index expressions!
CREATE INDEX idx_area ON circles (calculate_area(radius));
 
-- STABLE: Same result within a transaction
-- Cannot be used in indexes but optimizer can cache within query
CREATE OR REPLACE FUNCTION get_current_user_timezone()
RETURNS TEXT
LANGUAGE SQL
STABLE PARALLEL SAFE
AS $$
    SELECT current_setting('TIMEZONE');
$$;
 
-- VOLATILE: May return different results on each call
-- Default if not specified; optimizer cannot cache
CREATE OR REPLACE FUNCTION log_access_and_return(val INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    INSERT INTO access_log (accessed_at, value) VALUES (NOW(), val);
    RETURN val;
END;
$$;
 
-- SQL-language functions can be INLINED by PostgreSQL
-- This is like SQL Server's scalar UDF inlining but more mature
 
-- INLINABLE (simple SQL function)
CREATE OR REPLACE FUNCTION double_value(x INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT x * 2;
$$;
 
-- CHECK IF INLINE HAPPENED: EXPLAIN shows expression, not function call
EXPLAIN SELECT double_value(id) FROM test_table;
-- Should show: "id * 2" not "double_value(id)"
 
-- NOT INLINABLE (PL/pgSQL procedural function)
CREATE OR REPLACE FUNCTION double_value_plpgsql(x INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN x * 2;
END;
$$;
 
-- Performance TIP: Prefer LANGUAGE SQL over LANGUAGE plpgsql
-- for simple functions to enable inlining

PostgreSQL Function Inlining Rules

PostgreSQL can inline SQL-language functions (not PL/pgSQL) under certain conditions: function must be marked IMMUTABLE or STABLE, must contain a single SQL statement, and must not have security-related constraints. For maximum performance, prefer simple SQL-language functions with proper volatility declarations.

Scalar Functions

The Workhorse of SQL Functions

Scalar Function Fundamentals

Designing Effective Scalar Functions

Working with Different Data Types

Performance Implications

SCHEMABINDING and Determinism

SQL Server 2019+ Scalar UDF Inlining

PostgreSQL Perspectives

There’s more to understand.

Scalar Functions

The Workhorse of SQL Functions

Scalar Function Fundamentals

Designing Effective Scalar Functions

Working with Different Data Types

Performance Implications

SCHEMABINDING and Determinism

SQL Server 2019+ Scalar UDF Inlining

PostgreSQL Perspectives

There’s more to understand.