| title | Mathematical Functions (Transact-SQL) | |||
|---|---|---|---|---|
| description | Mathematical Transact-SQL functions in the SQL Server Database Engine. | |||
| author | markingmyname | |||
| ms.author | maghan | |||
| ms.reviewer | randolphwest | |||
| ms.date | 12/16/2024 | |||
| ms.service | sql | |||
| ms.subservice | t-sql | |||
| ms.topic | reference | |||
| ms.custom |
|
|||
| helpviewer_keywords |
|
|||
| dev_langs |
|
|||
| monikerRange | =azuresqldb-current || =azuresqldb-mi-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqledge-current || =azure-sqldw-latest || >=aps-pdw-2016 || =fabric || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
The following scalar functions perform a calculation, usually based on input values that you provide as arguments, and return a numeric value.
Functions in this category evaluate the magnitude or directional sign of a numeric input. Use them in data validation, normalization, financial modeling, and any scenario where the positivity, negativity, or absolute scale of a value needs to be interpreted or standardized.
| Function | Description |
|---|---|
| ABS | Returns the absolute (positive) value of the numeric expression. |
| SIGN | Returns +1, 0, or -1 depending on whether the expression is positive, zero, or negative. |
These functions convert floating-point or high-precision values into integers or fixed-precision representations. They support reporting, bucketing, currency formatting, threshold calculations, and any operation where values must align with discrete numeric boundaries.
| Function | Description |
|---|---|
| CEILING | Returns smallest integer greater than or equal to the expression. |
| FLOOR | Returns largest integer less than or equal the expression. |
| ROUND | Rounds a numeric value to the specified precision and length. |
This group provides the elementary trigonometric functions that compute ratios of a right triangle or model periodic behavior. In SQL workloads, these functions typically support geometric computation, spatial transformations, data analysis, and simulation models that require angle-based calculations.
| Function | Description |
|---|---|
| SIN | Sine of the specified angle. |
| COS | Cosine of the specified angle. |
| TAN | Tangent of the input expression. |
| COT | Cotangent of the specified angle. |
Inverse trigonometric functions return the angle that corresponds to a given trigonometric ratio. These functions enable you to recover an angle from coordinate or sensor data. Use them in navigation, geospatial analytics, error-vector calculations, and any scenario where you compute direction or orientation from component values.
| Function | Description |
|---|---|
| ASIN | Angle (in radians) whose sine is the given value (arcsine). |
| ACOS | Angle (in radians) whose cosine is the given value (arccosine). |
| ATAN | Angle (in radians) whose tangent is the given value (arctangent). |
| ATN2 | Angle (in radians) between the positive x-axis and a ray to point (y, x). |
These functions convert values between degrees and radians. They serve as utility operations that support interoperability with APIs, libraries, and mathematical formulas that expect a specific angular measurement unit.
| Function | Description |
|---|---|
| DEGREES | Converts radians to degrees. |
| RADIANS | Converts degrees to radians. |
This category includes functions that scale values exponentially, compute logarithmic magnitude, raise numbers to arbitrary powers, or extract roots. Typical workloads include financial compounding, scoring models, machine-learning feature engineering, scientific analysis, and any transformation involving nonlinear growth or decay.
| Function | Description |
|---|---|
| EXP | Exponential value of the expression (e raised to the expression). |
| LOG | Natural logarithm by default; optional base supported in SQL Server. |
| LOG10 | Base-10 logarithm. |
| POWER | Raises the expression to the specified power. |
| SQRT | Square root of the specified value. |
| SQUARE | Square of the specified value. |
These functions provide numerical constants and pseudo-random number generation for sampling, stochastic modeling, testing, and procedural computations. Use them for simulation, Monte Carlo analysis, randomized selection, or creating reproducible test scenarios when seeded.
| Function | Description |
|---|---|
| PI | Returns the constant π (pi). |
| RAND | Returns a pseudo-random float between 0 and 1. |
Arithmetic functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN, return a value with the same data type as the input value. Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE, and SQRT, cast their input values to float and return a float value.
All mathematical functions, except for RAND, are deterministic functions. This means they return the same results each time they're called with a specific set of input values. RAND is deterministic only when you specify a seed parameter. For more information about function determinism, see Deterministic and nondeterministic functions.