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 |
JSONPATHEXISTS(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 examplesreferences/window-functions.md - Window and ranking functions with frame specifications
