Loading learning content...
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.
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.
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).
123456789101112131415161718192021222324252627282930313233343536
-- Understanding scalar vs aggregate function behavior -- Sample data setupCREATE 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 rowCREATE FUNCTION dbo.LineTotal(@Qty INT, @Price DECIMAL(10,2))RETURNS DECIMAL(12, 2)ASBEGIN RETURN @Qty * @Price;END; -- Scalar invocation: function runs once PER ROWSELECT sale_id, quantity, unit_price, dbo.LineTotal(quantity, unit_price) AS line_total -- Called 10,000 times for 10,000 rowsFROM sales; -- Contrast with aggregate: runs on ALL rows, returns ONE valueSELECT SUM(quantity * unit_price) AS grand_total -- Single computation across all rowsFROM sales; -- Scalar in WHERE: still per-row evaluationSELECT * FROM sales WHERE dbo.LineTotal(quantity, unit_price) > 100; -- Called for every row being filteredWell-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:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117
-- 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 viewsASBEGIN -- 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 SCHEMABINDINGASBEGIN 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)ASBEGIN 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;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).
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.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
-- Numeric scalar functions: precision and edge cases -- Safe percentage calculation with overflow protectionCREATE FUNCTION dbo.SafePercentage( @Part DECIMAL(18, 4), @Whole DECIMAL(18, 4), @DecimalPlaces INT = 2)RETURNS DECIMAL(18, 4)WITH SCHEMABINDINGASBEGIN -- 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 roundingCREATE FUNCTION dbo.RoundCurrency( @Amount DECIMAL(18, 4), @CurrencyCode CHAR(3) = 'USD')RETURNS DECIMAL(18, 2)ASBEGIN 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 helperCREATE FUNCTION dbo.VarianceComponent(@Value DECIMAL(18,6), @Mean DECIMAL(18,6))RETURNS DECIMAL(18, 6)WITH SCHEMABINDINGASBEGIN IF @Value IS NULL OR @Mean IS NULL RETURN NULL; RETURN POWER(@Value - @Mean, 2);END;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.
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.
12345678910111213141516171819202122232425262728293031323334353637383940
-- Performance demonstration: Scalar UDF vs inline expression -- Setup: 1 million row test tableCREATE TABLE perf_test ( id INT PRIMARY KEY, value1 INT, value2 INT); -- Insert test dataINSERT INTO perf_test (id, value1, value2)SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.object_id), ABS(CHECKSUM(NEWID())) % 1000, ABS(CHECKSUM(NEWID())) % 1000FROM sys.objects a CROSS JOIN sys.objects b; -- Scalar function versionCREATE FUNCTION dbo.CalcRatio(@V1 INT, @V2 INT)RETURNS DECIMAL(10, 4)ASBEGIN 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_ratioFROM 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_ratioFROM perf_test;-- Typical time: < 1 second -- The difference can be 20x-100x depending on function complexity!| 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.
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.
12345678910111213141516171819202122232425262728293031323334353637383940
-- 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 viewsASBEGIN IF @Code IS NULL RETURN NULL; -- Remove all whitespace and convert to uppercase RETURN UPPER(REPLACE(REPLACE(REPLACE(@Code, ' ', ''), '-', ''), '.', ''));END;GO -- Verify determinismSELECT OBJECTPROPERTY(OBJECT_ID('dbo.NormalizeCustomerCode'), 'IsDeterministic');-- Returns 1 if deterministic -- Now we can use it in a computed columnALTER TABLE customersADD 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 INTASBEGIN -- 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 deterministicWhen 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 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.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
-- Check if a function is inlineableSELECT OBJECT_NAME(object_id) AS function_name, is_inlineableFROM sys.sql_modulesWHERE 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 exampleCREATE 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)ASBEGIN RETURN @Amount * @Rate;END;GO -- Check query plan - function should be inlinedSELECT product_id, price, dbo.CalculateTax(price, 0.08) AS taxFROM productsOPTION (RECOMPILE); -- Forces fresh plan to see inlining -- NON-INLINEABLE function (uses WHILE loop)CREATE FUNCTION dbo.FactorialLoop(@N INT)RETURNS BIGINTASBEGIN 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 functionCREATE OR ALTER FUNCTION dbo.MyFunction(@X INT)RETURNS INTWITH INLINE = OFF -- Disable inliningASBEGIN RETURN @X * 2;END; -- Database-level controlALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF; -- Disable globallyWhen 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 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.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
-- PostgreSQL volatility categories -- IMMUTABLE: Always returns same result for same inputs-- Best for optimizer, can be used in index expressionsCREATE OR REPLACE FUNCTION calculate_area(radius NUMERIC)RETURNS NUMERICLANGUAGE SQLIMMUTABLE PARALLEL SAFEAS $$ 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 queryCREATE OR REPLACE FUNCTION get_current_user_timezone()RETURNS TEXTLANGUAGE SQLSTABLE PARALLEL SAFEAS $$ SELECT current_setting('TIMEZONE');$$; -- VOLATILE: May return different results on each call-- Default if not specified; optimizer cannot cacheCREATE OR REPLACE FUNCTION log_access_and_return(val INTEGER)RETURNS INTEGERLANGUAGE plpgsqlVOLATILEAS $$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 INTEGERLANGUAGE SQLIMMUTABLEAS $$ SELECT x * 2;$$; -- CHECK IF INLINE HAPPENED: EXPLAIN shows expression, not function callEXPLAIN 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 INTEGERLANGUAGE plpgsqlIMMUTABLEAS $$BEGIN RETURN x * 2;END;$$; -- Performance TIP: Prefer LANGUAGE SQL over LANGUAGE plpgsql-- for simple functions to enable inliningPostgreSQL 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.
This section provides a collection of production-ready scalar functions that address common real-world requirements. Each example demonstrates best practices in design, error handling, and performance consideration.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137
-- =====================================================-- BUSINESS RULES: Tax calculation with jurisdiction-- =====================================================CREATE FUNCTION dbo.CalculateSalesTax( @Amount DECIMAL(18, 2), @StateCode CHAR(2), @ItemCategory VARCHAR(20))RETURNS DECIMAL(18, 2)WITH SCHEMABINDINGASBEGIN IF @Amount IS NULL OR @Amount <= 0 RETURN 0; IF @StateCode IS NULL RETURN NULL; DECLARE @Rate DECIMAL(6, 4) = 0; -- State base rates (simplified) SET @Rate = CASE @StateCode WHEN 'CA' THEN 0.0725 WHEN 'NY' THEN 0.0800 WHEN 'TX' THEN 0.0625 WHEN 'FL' THEN 0.0600 WHEN 'WA' THEN 0.0650 WHEN 'OR' THEN 0.0000 -- No sales tax WHEN 'MT' THEN 0.0000 -- No sales tax WHEN 'NH' THEN 0.0000 -- No sales tax ELSE 0.0500 -- Default rate END; -- Category exemptions/reductions IF @ItemCategory IN ('GROCERY', 'MEDICINE') SET @Rate = @Rate * 0.0; -- Many states exempt groceries ELSE IF @ItemCategory = 'CLOTHING' AND @StateCode IN ('NY', 'PA') SET @Rate = 0; -- Clothing exempt in some states RETURN ROUND(@Amount * @Rate, 2);END; -- =====================================================-- DATA QUALITY: Standardize company names-- =====================================================CREATE FUNCTION dbo.StandardizeCompanyName(@Name NVARCHAR(200))RETURNS NVARCHAR(200)ASBEGIN IF @Name IS NULL OR LEN(TRIM(@Name)) = 0 RETURN NULL; DECLARE @Result NVARCHAR(200) = TRIM(@Name); -- Remove common suffixes for matching SET @Result = REPLACE(@Result, ' INCORPORATED', ''); SET @Result = REPLACE(@Result, ' CORPORATION', ''); SET @Result = REPLACE(@Result, ' COMPANY', ''); SET @Result = REPLACE(@Result, ' LIMITED', ''); SET @Result = REPLACE(@Result, ', INC.', ''); SET @Result = REPLACE(@Result, ', INC', ''); SET @Result = REPLACE(@Result, ', LLC', ''); SET @Result = REPLACE(@Result, ', LTD', ''); SET @Result = REPLACE(@Result, ', CORP', ''); SET @Result = REPLACE(@Result, ' INC.', ''); SET @Result = REPLACE(@Result, ' INC', ''); SET @Result = REPLACE(@Result, ' LLC', ''); SET @Result = REPLACE(@Result, ' LTD', ''); SET @Result = REPLACE(@Result, ' CORP', ''); SET @Result = REPLACE(@Result, ' CO.', ''); SET @Result = REPLACE(@Result, ' CO', ''); -- Remove punctuation SET @Result = REPLACE(@Result, '.', ''); SET @Result = REPLACE(@Result, ',', ''); SET @Result = REPLACE(@Result, '''', ''); SET @Result = REPLACE(@Result, '"', ''); -- Collapse multiple spaces WHILE CHARINDEX(' ', @Result) > 0 SET @Result = REPLACE(@Result, ' ', ' '); RETURN UPPER(TRIM(@Result));END; -- =====================================================-- CONVERSION: Celsius to Fahrenheit with precision-- =====================================================CREATE FUNCTION dbo.CelsiusToFahrenheit(@Celsius DECIMAL(10, 4))RETURNS DECIMAL(10, 4)WITH SCHEMABINDINGASBEGIN IF @Celsius IS NULL RETURN NULL; RETURN (@Celsius * 9.0 / 5.0) + 32;END; -- ===================================================== -- SECURITY: Generate password strength score-- =====================================================CREATE FUNCTION dbo.PasswordStrengthScore(@Password NVARCHAR(100))RETURNS INT -- 0-100 scoreASBEGIN IF @Password IS NULL OR LEN(@Password) = 0 RETURN 0; DECLARE @Score INT = 0; DECLARE @Len INT = LEN(@Password); -- Length scoring (up to 30 points) SET @Score = @Score + CASE WHEN @Len >= 16 THEN 30 WHEN @Len >= 12 THEN 25 WHEN @Len >= 8 THEN 15 WHEN @Len >= 6 THEN 5 ELSE 0 END; -- Character variety (up to 40 points) IF @Password LIKE '%[a-z]%' SET @Score = @Score + 10; -- Lowercase IF @Password LIKE '%[A-Z]%' SET @Score = @Score + 10; -- Uppercase IF @Password LIKE '%[0-9]%' SET @Score = @Score + 10; -- Numbers IF @Password LIKE '%[^a-zA-Z0-9]%' SET @Score = @Score + 10; -- Special -- Sequence penalties (up to -20 points) IF @Password LIKE '%123%' OR @Password LIKE '%abc%' OR @Password LIKE '%qwerty%' SET @Score = @Score - 10; IF @Password = UPPER(@Password) OR @Password = LOWER(@Password) SET @Score = @Score - 10; -- No case mixing -- Bonus for diversity (up to 30 points) DECLARE @UniqueChars INT = LEN(@Password) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @Password, 'a', ''), 'e', ''), 'i', ''), 'o', ''), 'u', ''), ' ', ''), '1', '')); SET @Score = @Score + CASE WHEN @UniqueChars * 1.0 / @Len > 0.7 THEN 30 WHEN @UniqueChars * 1.0 / @Len > 0.5 THEN 15 ELSE 0 END; RETURN CASE WHEN @Score > 100 THEN 100 WHEN @Score < 0 THEN 0 ELSE @Score END;END;Scalar functions are the workhorses of SQL function development, transforming inputs into single output values one row at a time. Mastery of scalar functions requires understanding both their power and their limitations.
You now have deep knowledge of scalar functions—from fundamentals to advanced optimization techniques. The next page explores table-valued functions (TVFs), which return result sets instead of single values, opening up entirely different usage patterns and optimization opportunities.