07 — Functions: Scalar, Inline TVF, Multi-Statement TVF, System Functions, Inlining

User-Defined Functions (UDFs)
├── Scalar          -- Returns a single value; can be used anywhere an expression fits
│   ├── May be inlined by optimizer (2019+, with restrictions)
│   └── If not inlined: row-by-row execution, no stats, black box to optimizer
├── Inline TVF      -- Returns TABLE; single SELECT; full optimizer visibility
│   └── Think of it as a parameterized view
└── Multi-statement TVF (mTVF)
    ├── Returns @table_variable TABLE
    ├── Populated via procedural logic (IF, WHILE, multi-step)
    └── Optimizer sees fixed cardinality estimate (1 row pre-2017; interleaved 2017+)

CREATE OR ALTER FUNCTION dbo.fn_TaxAmount
(
    @amount    DECIMAL(18, 2),
    @tax_rate  DECIMAL(5, 4)  -- e.g. 0.0875 for 8.75%
)
RETURNS DECIMAL(18, 2)
WITH SCHEMABINDING         -- recommended; enables determinism if function is deterministic
AS
BEGIN
    RETURN @amount * @tax_rate;
END;
GO

-- Usage
SELECT
    order_id,
    subtotal,
    dbo.fn_TaxAmount(subtotal, 0.0875) AS tax
FROM dbo.Orders;

-- Scalar UDF in SELECT list
SELECT dbo.fn_TaxAmount(100.00, 0.10);          -- must qualify with schema name

-- Scalar UDF in WHERE clause (kills sargability if applied to an indexed column)
SELECT * FROM dbo.Orders WHERE dbo.fn_TaxAmount(subtotal, 0.10) > 5.00;
-- Better: compute the threshold, filter on raw column
SELECT * FROM dbo.Orders WHERE subtotal > 5.00 / 0.10;

-- Scalar UDF as computed column default
ALTER TABLE dbo.Orders
    ADD tax_amount AS dbo.fn_TaxAmount(subtotal, 0.0875);
-- Persisted computed column (requires DETERMINISTIC + PRECISE)
ALTER TABLE dbo.Orders
    ADD tax_amount AS dbo.fn_TaxAmount(subtotal, 0.0875) PERSISTED;

-- Check if a function is marked as inlineable
SELECT
    o.name,
    m.is_inlineable,
    m.inline_type
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.type = 'FN'
  AND o.name = 'fn_TaxAmount';

CREATE OR ALTER FUNCTION dbo.fn_NoInline (@x INT)
RETURNS INT
WITH INLINE = OFF          -- explicit opt-out
AS
BEGIN
    RETURN @x * 2;
END;

SELECT dbo.fn_TaxAmount(subtotal, 0.10) FROM dbo.Orders
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

CREATE OR ALTER FUNCTION dbo.fn_OrdersByCustomer
(
    @customer_id INT,
    @start_date  DATE,
    @end_date    DATE
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT
        o.order_id,
        o.order_date,
        o.total_amount,
        c.customer_name
    FROM dbo.Orders o
    JOIN dbo.Customers c ON c.customer_id = o.customer_id
    WHERE o.customer_id  = @customer_id
      AND o.order_date  >= @start_date
      AND o.order_date  <  DATEADD(DAY, 1, @end_date)
);
GO

-- Usage
SELECT * FROM dbo.fn_OrdersByCustomer(42, '2024-01-01', '2024-12-31');

-- CROSS APPLY pattern (lateral join — one call per outer row)
SELECT
    c.customer_id,
    c.customer_name,
    recent.order_id,
    recent.total_amount
FROM dbo.Customers c
CROSS APPLY dbo.fn_OrdersByCustomer(c.customer_id, '2024-01-01', '2024-12-31') AS recent;

CREATE OR ALTER FUNCTION dbo.fn_CustomerSummary
(
    @min_orders INT
)
RETURNS @result TABLE
(
    customer_id    INT          NOT NULL,
    customer_name  NVARCHAR(100) NOT NULL,
    order_count    INT          NOT NULL,
    total_spent    DECIMAL(18, 2) NOT NULL,
    PRIMARY KEY (customer_id)
)
AS
BEGIN
    INSERT INTO @result (customer_id, customer_name, order_count, total_spent)
    SELECT
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id),
        SUM(o.total_amount)
    FROM dbo.Customers c
    JOIN dbo.Orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.customer_name
    HAVING COUNT(o.order_id) >= @min_orders;

    -- Post-processing step that would be hard in a single SELECT
    UPDATE @result
    SET order_count = order_count + 1    -- hypothetical adjustment
    WHERE total_spent > 10000;

    RETURN;
END;
GO

-- Usage
SELECT * FROM dbo.fn_CustomerSummary(5);

-- Verify interleaved execution was used:
-- Look for "IsInterleavedExecuted=1" attribute on the TVF operator in an actual XML plan.

-- Check determinism of a function
SELECT
    o.name,
    m.is_deterministic,
    m.uses_database_collation,
    m.is_inlineable
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.type IN ('FN', 'IF', 'TF')
  AND o.name = 'fn_TaxAmount';

CREATE OR ALTER FUNCTION dbo.fn_FullName
(
    @first NVARCHAR(50),
    @last  NVARCHAR(50)
)
RETURNS NVARCHAR(101)
WITH SCHEMABINDING   -- pure computation, no table access → deterministic
AS
BEGIN
    RETURN LTRIM(RTRIM(@first)) + N' ' + LTRIM(RTRIM(@last));
END;
GO

-- Built-in: STRING_AGG as replacement for STUFF+FOR XML PATH
SELECT
    customer_id,
    STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM dbo.OrderItems
GROUP BY customer_id;

-- @@IDENTITY      : Last identity value inserted in current session, ANY table
-- SCOPE_IDENTITY(): Last identity value in current scope (proc/trigger/function)
-- IDENT_CURRENT('table'): Last identity value inserted into named table by ANY session

-- Safe pattern: always use SCOPE_IDENTITY()
INSERT INTO dbo.Orders (customer_id, order_date) VALUES (1, GETDATE());
DECLARE @new_id INT = SCOPE_IDENTITY();

-- @@IDENTITY gotcha: fires AFTER a trigger that also inserts into a table with IDENTITY
-- → @@IDENTITY returns the trigger's identity, not the base table's
-- → SCOPE_IDENTITY() returns the base table's identity (correct)

-- AT TIME ZONE (2016+): convert datetime to datetimeoffset
SELECT SYSDATETIME() AT TIME ZONE 'Eastern Standard Time';
-- Convert between zones
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time';

ISNULL(@val, default_val)        -- SQL Server-specific; returns same type as first arg
COALESCE(@a, @b, @c)             -- ANSI SQL; returns first non-NULL; type determined by highest precedence
NULLIF(@a, @b)                   -- returns NULL if @a = @b, else @a

-- ISNULL vs COALESCE type precedence trap:
DECLARE @x VARCHAR(3) = 'abc';
SELECT ISNULL(@x, 'default')     -- returns VARCHAR(3) → 'abc' (fits)
SELECT COALESCE(@x, 'default')   -- returns VARCHAR(7) due to type resolution
-- This matters when feeding result into a typed column or function

ABS(-5)                          -- 5
CEILING(4.1)                     -- 5
FLOOR(4.9)                       -- 4
ROUND(4.567, 2)                  -- 4.570
ROUND(4.567, 2, 1)               -- 4.560  (third arg=1: truncate, not round)
POWER(2, 10)                     -- 1024.0 (returns FLOAT unless base is INT)
SQRT(16.0)                       -- 4.0
LOG(100, 10)                     -- 2.0 (log base 10)
LOG(EXP(1))                      -- 1.0 (natural log)
PI()                             -- 3.14159265358979
SIGN(-7)                         -- -1
RAND()                           -- non-deterministic float [0,1)
RAND(42)                         -- seeded (but RAND(seed) only reseeds once per query)

LEN('hello ')                    -- 5 (ignores trailing spaces)
DATALENGTH(N'hello ')            -- 12 (bytes; nvarchar uses 2 bytes/char + trailing space)
CHARINDEX('lo', 'hello')         -- 4 (1-based)
PATINDEX('%[0-9]%', 'abc3def')   -- 4
SUBSTRING('hello', 2, 3)         -- 'ell'
LEFT('hello', 3)                 -- 'hel'
RIGHT('hello', 3)                -- 'llo'
LTRIM('  hi ')                   -- 'hi '
RTRIM('  hi ')                   -- '  hi'
TRIM('  hi  ')                   -- 'hi'  (2017+; also trims specific chars: TRIM('x' FROM 'xhix'))
UPPER('hello')                   -- 'HELLO'
LOWER('HELLO')                   -- 'hello'
REPLACE('abcabc', 'a', 'X')      -- 'XbcXbc'
STUFF('hello', 2, 3, 'XY')       -- 'hXYo'  (delete 3 chars at pos 2, insert 'XY')
CONCAT('a', NULL, 'b')           -- 'ab'  (NULL-safe concatenation)
CONCAT_WS(', ', 'a', NULL, 'b')  -- 'a, b'  (2017+; skips NULLs)
FORMAT(1234567.89, 'N2', 'en-US')-- '1,234,567.89'  (culture-aware; CLR-backed, slow)
STRING_SPLIT('a,b,c', ',')       -- rowset: value column ('a','b','c')

-- 2022+: ordinal column
SELECT value, ordinal
FROM STRING_SPLIT('a,b,c', ',', 1)
ORDER BY ordinal;

CAST('2024-01-15' AS DATE)
CONVERT(VARCHAR(10), GETDATE(), 120)    -- ISO 8601: '2024-01-15'
TRY_CAST('abc' AS INT)                  -- returns NULL on failure (safe)
TRY_CONVERT(INT, 'abc')                 -- returns NULL on failure (safe)
TRY_PARSE('January 15 2024' AS DATE USING 'en-US')  -- culture-aware parse

-- PARSE is CLR-backed and slower than CONVERT; use only when culture parsing is needed

-- Correct: window function inside an inline TVF
CREATE OR ALTER FUNCTION dbo.fn_RankedOrders (@customer_id INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT
        order_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn
    FROM dbo.Orders
    WHERE customer_id = @customer_id
);
GO

-- Get the most recent order per customer using CROSS APPLY
SELECT c.customer_id, c.customer_name, r.order_id, r.total_amount
FROM dbo.Customers c
CROSS APPLY (
    SELECT TOP (1) order_id, total_amount
    FROM dbo.fn_RankedOrders(c.customer_id)
    ORDER BY rn
) r;

-- Drop if exists (2016+)
DROP FUNCTION IF EXISTS dbo.fn_TaxAmount;

-- Create or alter (2016+; preferred over DROP + CREATE)
CREATE OR ALTER FUNCTION dbo.fn_TaxAmount (...) ...

-- Pre-2016: drop and recreate pattern
IF OBJECT_ID(N'dbo.fn_TaxAmount', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_TaxAmount;
GO
CREATE FUNCTION dbo.fn_TaxAmount ...
GO

-- List all functions in a database
SELECT
    o.name,
    o.type_desc,        -- SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_TABLE_VALUED_FUNCTION
    o.create_date,
    o.modify_date,
    m.is_deterministic,
    m.is_inlineable
FROM sys.objects o
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.type IN ('FN', 'IF', 'TF')
ORDER BY o.name;

-- View function definition
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fn_TaxAmount'));
-- or
EXEC sp_helptext 'dbo.fn_TaxAmount';

-- Bad: 10M row table scan, UDF called 10M times
SELECT * FROM dbo.BigTable WHERE dbo.fn_SlowUDF(col) = 1;

-- Fix option 1: Inline the logic
SELECT * FROM dbo.BigTable WHERE (col * 0.0875) > 5.00;

-- Fix option 2: Persisted computed column + index
ALTER TABLE dbo.BigTable ADD computed_val AS dbo.fn_DetUDF(col) PERSISTED;
CREATE INDEX IX_BigTable_computed ON dbo.BigTable (computed_val);

-- Will fail: GETDATE() is non-deterministic
ALTER TABLE dbo.T ADD created_at AS GETDATE() PERSISTED;
-- Error: Computed column 'created_at' in table 'T' cannot be persisted because the column is non-deterministic.

-- Fix: Use a DEFAULT constraint instead
ALTER TABLE dbo.T ADD created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME();

-- If parallelism is critical, convert to inline TVF or materialize into a temp table
-- and then join/apply against the temp table inside a stored procedure.

-- Check dependencies before altering
SELECT
    OBJECT_NAME(referencing_id) AS depends_on_this,
    OBJECT_NAME(referenced_id)  AS function_to_change
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('dbo.fn_B');

-- Slow:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')
-- Fast:
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)