Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Mathematical Functions Supported by SQL Server 2005

By: FYIcenter.com

(Continued from previous topic...)

What Are the Mathematical Functions Supported by SQL Server 2005?

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 retuns 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

(Continued on next topic...)

1. What Is an Expression?
2. What Are Arithmetic Operators?
3. What Happens to an Arithmetic Operation with Two Different Data Types?
4. How To Convert a Numeric Expression from One Data Type to Another?
5. How To Convert Numeric Expression Data Types by Assignment Operations?
6. How To Convert Numeric Expression Data Types using the CAST() Function?
7. How To Convert Numeric Expression Data Types using the CONVERT() Function?
8. How To Convert Character Strings into Numeric Values?
9. What Happens When Converting Big Values to Integers?
10. What Happens When Converting Big Values to NUMERIC Data Types?
11. What Are the Mathematical Functions Supported by SQL Server 2005?
12. How To Convert Numeric Values to Integers?
13. How To Round a Numeric Value To a Specific Precision?
14. How To Generate Random Numbers with the RAND() Function?