What Are the Mathematical Functions Supported by SQL Server 2005 in SQL Server Transact-SQL?

SQL Server 2005 supports 23 mathematical functions: ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, and TAN.

The return data types of mathematical functions are determined by two rules:

• Arithmetical functions: ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN are returning the same data type as the input values. No implicit data type conversions are performed.
• Other mathematical functions: COS, COT, EXP, LOG, LOG10, SIN, SQRT, SQUARE and TAN are taking only FLOAT data type as input and returning FLOAT data type. Implicit data type conversion will be performed if you are calling these functions with a non-FLOAT data type.

The tutorial exercise gives you some good examples on how to use mathematical functions:

-- ABS returns the same data type as the input
DECLARE @x FLOAT(53);
DECLARE @y NUMERIC(9,2);
DECLARE @z INT;
SET @x = -12345.123456789E+20;
SET @y = -12345.12;
SET @z = -12345
SELECT ABS(@x);
SELECT ABS(@y);
SELECT ABS(@z);
GO
1.2345123456789E+24
12345.12
12345

-- SQRT converts input to FLOAT(53) first
DECLARE @x FLOAT(53);
DECLARE @y NUMERIC(9,2);
DECLARE @z INT;
SET @x = 12345.123456789E+20;
SET @y = 12345.12;
SET @z = 12345
SELECT SQRT(@x);
SELECT SQRT(@y);
SELECT SQRT(@z);
GO
1111086110829.8
111.108595527079
111.108055513541

⇒ FLOOR, CEILING, ROUND - Converting Values to Integers in SQL Server

⇐ Overflow Errors on Converting Big Values to NUMERIC in SQL Server

⇑ Numeric Expressions and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials