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
1---2name: tsql-functions3description: Complete T-SQL function reference for SQL Server and Azure SQL Database. PROACTIVELY activate for: (1) string functions (CONCAT_WS, STRING_SPLIT, STRING_AGG, TRIM, REPLACE, LEFT/RIGHT/SUBSTRING), (2) date/time functions (DATEADD, DATEDIFF, FORMAT, DATETRUNC, AT TIME ZONE), (3) math and conversion functions (CAST, CONVERT, TRY_CAST, ROUND, FLOOR, CEILING), (4) window/ranking functions (ROW_NUMBER, RANK, LEAD/LAG, FIRST_VALUE, LAST_VALUE, NTILE), (5) JSON functions (JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON), (6) XML functions (FOR XML, .nodes, .value), (7) aggregate functions and GROUP BY extensions, (8) system and metadata functions (sys.* views, OBJECT_ID, OBJECT_NAME). Provides: function catalog grouped by category, version-availability matrix (SQL 2016+/2019+/2022+), and worked examples for each function family.4---5
6# T-SQL Functions Reference
7
8Complete reference for all T-SQL function categories with version-specific availability.
9
10## Quick Reference
11
12### String Functions
13| Function | Description | Version |
14|----------|-------------|---------|
15| `CONCAT(str1, str2, ...)` | NULL-safe concatenation | 2012+ |
16| `CONCAT_WS(sep, str1, ...)` | Concatenate with separator | 2017+ |
17| `STRING_AGG(expr, sep)` | Aggregate strings | 2017+ |
18| `STRING_SPLIT(str, sep)` | Split to rows | 2016+ |
19| `STRING_SPLIT(str, sep, 1)` | With ordinal column | 2022+ |
20| `TRIM([chars FROM] str)` | Remove leading/trailing | 2017+ |
21| `TRANSLATE(str, from, to)` | Character replacement | 2017+ |
22| `FORMAT(value, format)` | .NET format strings | 2012+ |
23
24### Date/Time Functions
25| Function | Description | Version |
26|----------|-------------|---------|
27| `DATEADD(part, n, date)` | Add interval | All |
28| `DATEDIFF(part, start, end)` | Difference (int) | All |
29| `DATEDIFF_BIG(part, s, e)` | Difference (bigint) | 2016+ |
30| `EOMONTH(date, [offset])` | Last day of month | 2012+ |
31| `DATETRUNC(part, date)` | Truncate to precision | 2022+ |
32| `DATE_BUCKET(part, n, date)` | Group into buckets | 2022+ |
33| `AT TIME ZONE 'tz'` | Timezone conversion | 2016+ |
34
35### Window Functions
36| Function | Description | Version |
37|----------|-------------|---------|
38| `ROW_NUMBER()` | Sequential unique numbers | 2005+ |
39| `RANK()` | Rank with gaps for ties | 2005+ |
40| `DENSE_RANK()` | Rank without gaps | 2005+ |
41| `NTILE(n)` | Distribute into n groups | 2005+ |
42| `LAG(col, n, default)` | Previous row value | 2012+ |
43| `LEAD(col, n, default)` | Next row value | 2012+ |
44| `FIRST_VALUE(col)` | First in window | 2012+ |
45| `LAST_VALUE(col)` | Last in window | 2012+ |
46| `IGNORE NULLS` | Skip NULLs in offset funcs | 2022+ |
47
48### SQL Server 2022 New Functions
49| Function | Description |
50|----------|-------------|
51| `GREATEST(v1, v2, ...)` | Maximum of values |
52| `LEAST(v1, v2, ...)` | Minimum of values |
53| `DATETRUNC(part, date)` | Truncate date |
54| `GENERATE_SERIES(start, stop, [step])` | Number sequence |
55| `JSON_OBJECT('key': val)` | Create JSON object |
56| `JSON_ARRAY(v1, v2, ...)` | Create JSON array |
57| `JSON_PATH_EXISTS(json, path)` | Check path exists |
58| `IS [NOT] DISTINCT FROM` | NULL-safe comparison |
59
60## Core Patterns
61
62### String Manipulation
63```sql
64-- Concatenate with separator (NULL-safe)
65SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName
66
67-- Split string to rows with ordinal
68SELECT value, ordinal
69FROM STRING_SPLIT('apple,banana,cherry', ',', 1)
70
71-- Aggregate strings with ordering
72SELECT DeptID,
73 STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
74FROM Employees
75GROUP BY DeptID
76```
77
78### Date Operations
79```sql
80-- Truncate to first of month
81SELECT DATETRUNC(month, OrderDate) AS MonthStart
82
83-- Group by week buckets
84SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
85 COUNT(*) AS OrderCount
86FROM Orders
87GROUP BY DATE_BUCKET(week, 1, OrderDate)
88
89-- Generate date series
90SELECT CAST(value AS date) AS Date
91FROM GENERATE_SERIES(
92 CAST('2024-01-01' AS date),
93 CAST('2024-12-31' AS date),
94 1
95)
96```
97
98### Window Functions
99```sql
100-- Running total with partitioning
101SELECT OrderID, CustomerID, Amount,
102 SUM(Amount) OVER (
103 PARTITION BY CustomerID
104 ORDER BY OrderDate
105 ROWS UNBOUNDED PRECEDING
106 ) AS RunningTotal
107FROM Orders
108
109-- Get previous non-NULL value (SQL 2022+)
110SELECT Date, Value,
111 LAST_VALUE(Value) IGNORE NULLS OVER (
112 ORDER BY Date
113 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
114 ) AS PreviousNonNull
115FROM Measurements
116```
117
118### JSON Operations
119```sql
120-- Extract scalar value
121SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName
122
123-- Parse JSON array to rows
124SELECT j.ProductID, j.Quantity
125FROM Orders
126CROSS APPLY OPENJSON(OrderDetails)
127WITH (
128 ProductID INT '$.productId',
129 Quantity INT '$.qty'
130) AS j
131
132-- Build JSON object (SQL 2022+)
133SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
134FROM Customers
135```
136
137## Additional References
138
139For deeper coverage of specific function categories, see:
140
141- `references/string-functions.md` - Complete string function reference with examples
142- `references/window-functions.md` - Window and ranking functions with frame specifications
josiahsiegel