Using Mathematical Functions

A mathematical function performs a mathematical operation on numeric expressions and returns the result of the operation. Mathematical functions operate on the Microsoft® SQL Server™ system-supplied numeric data (decimal, integer, float, real, money, smallmoney, smallint, and tinyint). The precision of built-in operations on float data type data is six decimal places by default.

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 this SELECT statement is a decimal, and FLOOR returns 123, which is a decimal value:

SELECT FLOOR(123.45)

---------------------

123

(1 row(s) affected)

But, this 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 between -1.00 and 1.00. If a range of -2 is specified, a domain error occurs.

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

The 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:

SET ARITHABORT ON, which terminates the query and quits the user-defined transaction. The SET ARITHABORT setting overrides the setting for SET ANSI_WARNINGS.
SET ANSI_WARNINGS ON, which stops the command.
SET ARITHIGNORE ON, which causes no warning message to be displayed. Both the SET ARITHABORT and SET ANSI_WARNINGS settings override the SET ARITHIGNORE setting.

If neither 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, SET ANSI_WARNINGS, and SET ARITHIGNORE.

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