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