Built-in functions

Status: Draft (deep-dive v1)
Stack note: PowerShell and Blazor should stay thin — do shaping in set-based T-SQL inside usp_* with built-ins. Custom reuse belongs in User-defined functions (prefer inline TVFs).
Pairs with: SQL-11 (UDFs), SQL-25 (paging), SQL-26 (JSON), SQL-30 (CTEs).
Goal: Reach for the right built-in family quickly; avoid scalar-UDF habits when a built-in expression works.

Quick chooser

Need Reach for Avoid
Trim / split / search strings TRIM, CONCAT_WS, STRING_AGG, STRING_SPLIT Cursor char loops; scalar UDF wrappers
Dates for boards / ranges DATEADD, DATEDIFF, DATETRUNC (newer), SYSUTCDATETIME Local GETDATE() mixed into UTC pipelines
Type conversion TRY_CONVERT, TRY_CAST Bare CONVERT that aborts a batch on junk
Running totals / ranks Window: SUM() OVER, ROW_NUMBER Temp + self-join unless needed
Rollups for heat maps COUNT, SUM, AVG, MIN/MAX Row-by-row accumulation
JSON from PS/Blazor JSON_VALUE / OPENJSON (SQL-26) String scrape

String

SELECT TRIM(N'  x  ') AS t,
       CONCAT_WS(N'/', N'a', NULL, N'c') AS path,  -- skips NULL
       STRING_AGG(HostName, N', ') WITHIN GROUP (ORDER BY HostName) AS hosts
FROM dbo.SqlInstance
WHERE IsActive = 1;

-- Split (use carefully; ordinal available on newer engines)
SELECT value FROM STRING_SPLIT(N'a,b,c', N',');

Ops tips: Prefer CONCAT_WS over col + '/' + col (NULL poisoning). STRING_AGG needs a plan for very large groups (length limits).

Date / time

DECLARE @Utc datetime2(3) = SYSUTCDATETIME();

SELECT DATEADD(day, -7, @Utc) AS week_ago,
       DATEDIFF(hour, LastSeenAt, @Utc) AS hours_stale,
       CAST(@Utc AS date) AS utc_date;

Stack tip: Store and compute UTC in SQL; let Blazor format local time. Mixing GETDATE() and UTC in the same RAG rule creates false alerts.

Conversion

SELECT TRY_CONVERT(int, N'42') AS ok,
       TRY_CONVERT(int, N'n/a') AS null_instead_of_blow_up,
       FORMAT(1234.5, N'N2') AS display_only;  -- not for predicates

Rule: Predicates and joins use raw types + TRY_*. FORMAT is for display paths (often better in Blazor).

Math & aggregate

SELECT EnvironmentCode,
       COUNT(*) AS instances,
       SUM(CASE WHEN RagStatus = N'Red' THEN 1 ELSE 0 END) AS reds,
       AVG(CAST(SizeGb AS decimal(18,2))) AS avg_size_gb
FROM dbo.SqlInstanceVolume
GROUP BY EnvironmentCode;

Use COUNT(*) vs COUNT(col) deliberately (NULLs). Filtered aggregates via SUM(CASE…) beat multiple scans for simple board metrics.

Window functions

SELECT AlertId, SqlInstanceId, SeverityCode, FirstSeenAt,
       ROW_NUMBER() OVER (
         PARTITION BY SqlInstanceId
         ORDER BY SeverityCode DESC, FirstSeenAt DESC
       ) AS rn,
       SUM(1) OVER (PARTITION BY SqlInstanceId) AS open_on_instance
FROM dbo.Alert
WHERE Status = N'Open';

Pairs with Pagination and CTEs: filter → window → slice.

Built-in vs UDF (SQL-11)

Situation Prefer
One-off expression Built-in inline
Reused set shape (parameterized) Inline TVF (SQL-11)
Procedural multi-step Stored proc, not scalar UDF
JSON / string / date Built-ins first; UDF only if it stays inline-friendly

Ops checklist

See also