Mathematical Functions
In this article and the four that follow it, I will describe the mathematical functions provided by SQL Server 2005's version of Transact-SQL. These have been grouped into the following categories:
All of the functions that will be described work with numeric data and return numeric results. Each function can be used within queries, create and update statements and directly, via a tool such as SQL Server Management Studio. For simplicity, the code samples provided will be directly executable without the requirement to be connected to a specific database.
Abs
The Abs function returns the absolute value of its parameter. It converts negative values to positive values but leaves positive values unchanged. The value being processed can be any numeric data type and the function returns a value of the same type.
The following example shows the results of the abs function for several positive and negative values.
SELECT abs(1), abs(-1), abs(-123.45) -- 1, 1, 123.45
When using signed integers, the range of negative values is larger than the range of positive values. This is due to the use of two's complement arithmetic for storing integers. This gives the possibility that the abs function can cause an overflow error when used with the smallest possible value.
To demonstrate, execute the following sample. This causes an overflow error because the largest value that can be represented by an Int is 2,147,483,647.
DECLARE @TooBig INT
SET @TooBig = -2147483648
PRINT abs(@TooBig)
Sign
The Sign function can be used to determine whether a value is negative or positive. The function returns -1 for negative numbers, 1 for positive numbers or 0 if the input parameter is zero. The returned value uses the same data type as the parameter.
SELECT sign(1), sign(-1), sign(0), sign(-123.45) -- 1, -1, 0, -1
Sqrt
If you need to determine the square root of a value, you can use the Sqrt function. This accepts a parameter that must be a Float, or a data type that can be implicitly converted to a Float. The return value is also a Float.
SELECT sqrt(256), sqrt(257) -- 16, 16.0312195418814
Square
The Square function returns the square of a value. ie. the result of multiplying the value by itself. As with Sqrt, the parameter of the function must be a Float or compatible data type. The result is also a Float.
SELECT square(16), square(3.5) -- 256, 12.25
A common use of the Square and Sqrt functions is to calculate the length of the hypotenuse in a right-angled triangle, given the lengths of the other two sides. The following uses Pythagorean theorem to perform this calculation for a (3, 4, 5) triangle.
SELECT sqrt(square(3) + square(4)) -- 5
Power
The last function in this article is Power. This raises one value to the power of another. The first parameter contains the value to be raised and the second parameter specifies the power to use. The return value has the same data type as the first parameter.
13 October 2009