SQL 2005+

# SQL Server Basic Mathematical Functions

*The thirty-ninth part of the SQL Server Programming Fundamentals tutorial begins an examination of mathematical functions provided by Transact-SQL (T-SQL). This article describes five basic functions. Future articles will look at more advanced functions.*

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

- Basic Mathematical Functions
- Rounding Functions
- Trigonometric Functions
- Logarithmic Functions
- Random Number Generation Functions

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.

SELECT power(3,5) -- 243