T-SQL Functions Reference
Complete reference for all T-SQL function categories with version-specific availability.
Quick Reference String Functions Function Description Version CONCAT(str1, str2, ...) NULL-safe concatenation 2012+ CONCAT_WS(sep, str1, ...) Concatenate with separator 2017+ STRING_AGG(expr, sep) Aggregate strings 2017+ STRING_SPLIT(str, sep) Split to rows 2016+ STRING_SPLIT(str, sep, 1) With ordinal column 2022+ TRIM([chars FROM] str) Remove leading/trailing 2017+ TRANSLATE(str, from, to) Character replacement 2017+ FORMAT(value, format) .NET format strings 2012+ Date/Time Functions Function Description Version DATEADD(part, n, date) Add interval All DATEDIFF(part, start, end) Difference (int) All DATEDIFF_BIG(part, s, e) Difference (bigint) 2016+ EOMONTH(date, [offset]) Last day of month 2012+ DATETRUNC(part, date) Truncate to precision 2022+ DATE_BUCKET(part, n, date) Group into buckets 2022+ AT TIME ZONE 'tz' Timezone conversion 2016+ Window Functions Function Description Version ROW_NUMBER() Sequential unique numbers 2005+ RANK() Rank with gaps for ties 2005+ DENSE_RANK() Rank without gaps 2005+ NTILE(n) Distribute into n groups 2005+ LAG(col, n, default) Previous row value 2012+ LEAD(col, n, default) Next row value 2012+ FIRST_VALUE(col) First in window 2012+ LAST_VALUE(col) Last in window 2012+ IGNORE NULLS Skip NULLs in offset funcs 2022+ SQL Server 2022 New Functions Function Description GREATEST(v1, v2, ...) Maximum of values LEAST(v1, v2, ...) Minimum of values DATETRUNC(part, date) Truncate date GENERATE_SERIES(start, stop, [step]) Number sequence JSON_OBJECT('key': val) Create JSON object JSON_ARRAY(v1, v2, ...) Create JSON array JSON_PATH_EXISTS(json, path) Check path exists IS [NOT] DISTINCT FROM NULL-safe comparison Core Patterns String Manipulation -- Concatenate with separator (NULL-safe) SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName
-- Split string to rows with ordinal SELECT value, ordinal FROM STRING_SPLIT('apple,banana,cherry', ',', 1)
-- Aggregate strings with ordering SELECT DeptID, STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate) FROM Employees GROUP BY DeptID
Date Operations -- Truncate to first of month SELECT DATETRUNC(month, OrderDate) AS MonthStart
-- Group by week buckets SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket, COUNT(*) AS OrderCount FROM Orders GROUP BY DATE_BUCKET(week, 1, OrderDate)
-- Generate date series SELECT CAST(value AS date) AS Date FROM GENERATE_SERIES( CAST('2024-01-01' AS date), CAST('2024-12-31' AS date), 1 )
Window Functions -- Running total with partitioning SELECT OrderID, CustomerID, Amount, SUM(Amount) OVER ( PARTITION BY CustomerID ORDER BY OrderDate ROWS UNBOUNDED PRECEDING ) AS RunningTotal FROM Orders
-- Get previous non-NULL value (SQL 2022+) SELECT Date, Value, LAST_VALUE(Value) IGNORE NULLS OVER ( ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS PreviousNonNull FROM Measurements
JSON Operations -- Extract scalar value SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName
-- Parse JSON array to rows SELECT j.ProductID, j.Quantity FROM Orders CROSS APPLY OPENJSON(OrderDetails) WITH ( ProductID INT '$.productId', Quantity INT '$.qty' ) AS j
-- Build JSON object (SQL 2022+) SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson FROM Customers
Additional References
For deeper coverage of specific function categories, see:
references/string-functions.md - Complete string function reference with examples references/window-functions.md - Window and ranking functions with frame specifications