# Using Mathematical Functions (Transact-SQL)

A mathematical function performs a mathematical operation on numeric expressions and returns the result of the operation. Mathematical functions operate on the SQL Server 2005 system-supplied numeric data: **decimal**, **integer**, **float**, **real**, **money**, **smallmoney**, **smallint**, and **tinyint**. By default, the precision of built-in operations on **float** data type data is six decimal places.

By default, a number passed to a mathematical function will be interpreted as a **decimal** data type. The CAST or CONVERT functions can be used to change the data type to something else, such as a **float**. For example, the value returned by the FLOOR function has the data type of the input value. The input of the following `SELECT`

statement is a **decimal**. `FLOOR`

returns `123`

. This is a decimal value.

```
SELECT FLOOR(123.45)
---------------------
123
(1 row(s) affected)
```

But, the following example uses a `float`

value and `FLOOR`

returns a `float`

value:

```
SELECT FLOOR (CONVERT (float, 123.45))
-------------------------------------
123.000000
(1 row(s) affected)
```

A floating point underflow error occurs when the **float** or **real** result of a mathematical function is too small to display. A result of 0.0 is returned and no error message is displayed. For example, the mathematical calculation of 2 to the -100.0 power has a result 0.0.

Domain errors occur when the value provided in the mathematical function is not a valid value. For example, values specified for the ASIN function must be from -1.00 through 1.00. If a range of -2 is specified, a domain error occurs.

Range errors occur when the value specified is outside the allowed values. For example, POWER(10.0, 400) is out of the range of the maximum of ~2e+308 of the **float** data type, and POWER(-10.0, 401) is out of the range of the minimum of ~ -2e+308 of the **float** data type.

The following table shows mathematical functions that produce either a domain or range error.

Mathematical function | Result |
---|---|

SQRT(-1) |
Domain error. |

POWER(10.0, 400) |
Arithmetic Overflow error. |

POWER(10.0, -400) |
Value of 0.0 (floating point underflow). |

Error traps are provided to handle domain or range errors of these functions. You can use the following:

- SET ARITHABORT ON. This terminates the query and ends the user-defined transaction. The SET ARITHABORT setting overrides the setting for SET ANSI_WARNINGS.
- SET ANSI_WARNINGS ON. This stops the command.
- SET ARITHIGNORE ON. This prevents the display of a warning message. Both the SET ARITHABORT and SET ANSI_WARNINGS settings override the SET ARITHIGNORE setting.

If none of these options is set, SQL Server returns NULL and returns a warning message after the query is executed. For more information, see SET ARITHABORT (Transact-SQL), SET ANSI_WARNINGS (Transact-SQL), and SET ARITHIGNORE (Transact-SQL).

Internal conversion to **float** can cause loss of precision if either the **money** or **numeric** data types are used.

## See Also

#### Concepts

#### Other Resources

CAST and CONVERT (Transact-SQL)

FLOOR (Transact-SQL)

Mathematical Functions (Transact-SQL)