MSDN Documentation

SQL Server Documentation - Reference

Scalar Math Functions

These functions perform mathematical operations on input values and return a single value (scalar) as the result.

ABS ( Transact-SQL )

Returns the absolute value of a numeric expression.

ABS ( numeric_expression )
Parameters
  • numeric_expression: Required. The expression of the exact numeric or approximate numeric data type category is a valid.
    The following data types are supported: bit, tinyint, smallint, int, bigint, decimal, numeric, float, real, money, smallmoney, varchar, nvarchar, char, nchar.
Returns
  • Returns the data type of the input expression, except for bit data types. For bit data types, ABS returns int.

Example

SELECT ABS(-10.5); -- Returns 10.5
SELECT ABS(20); -- Returns 20

CEILING ( Transact-SQL )

Returns the smallest integer that is greater than or equal to a specified numeric expression.

CEILING ( numeric_expression )
Parameters
  • numeric_expression: An expression of the exact numeric or approximate numeric data type category is a valid.
Returns
  • Returns the same data type as the input expression, provided the input is an integer type. If the input is a non-integer numeric type, it returns the closest data type from the exact numeric or approximate numeric category that can hold the result.

Example

SELECT CEILING(12.345); -- Returns 13
SELECT CEILING(-7.89); -- Returns -7

FLOOR ( Transact-SQL )

Returns the largest integer that is less than or equal to a specified numeric expression.

FLOOR ( numeric_expression )
Parameters
  • numeric_expression: An expression of the exact numeric or approximate numeric data type category is a valid.
Returns
  • Returns the same data type as the input expression, provided the input is an integer type. If the input is a non-integer numeric type, it returns the closest data type from the exact numeric or approximate numeric category that can hold the result.

Example

SELECT FLOOR(12.345); -- Returns 12
SELECT FLOOR(-7.89); -- Returns -8

ROUND ( Transact-SQL )

Rounds a numeric expression to a specified length or precision, either to the nearest value or to zero.

ROUND ( numeric_expression, length [, function ] )
Parameters
  • numeric_expression: The expression to be rounded.
  • length: The number of digits to which numeric_expression is to be rounded. length must be a value of a small integer type. If length is positive, numeric_expression is rounded to the right of the decimal point. If length is negative, numeric_expression is rounded to the left of the decimal point.
  • function: An optional argument. The presence of this argument makes ROUND a function that rounds away from zero. If function is not supplied, ROUND rounds to the nearest value.
Returns
  • Returns the same data type as the input expression, provided the input is an integer type. If the input is a non-integer numeric type, it returns the closest data type from the exact numeric or approximate numeric category that can hold the result.

Example

SELECT ROUND(123.456, 2); -- Returns 123.46
SELECT ROUND(123.456, 0); -- Returns 123
SELECT ROUND(123.456, -1); -- Returns 120
SELECT ROUND(123.456, 2, 1); -- Returns 123.46 (rounds away from zero)

SQUARE ( Transact-SQL )

Returns the square of a numeric expression.

SQUARE ( numeric_expression )
Parameters
  • numeric_expression: An expression of the exact numeric or approximate numeric data type category is a valid.
Returns
  • Returns the same data type as the input expression, provided the input is an integer type. If the input is a non-integer numeric type, it returns the closest data type from the exact numeric or approximate numeric category that can hold the result.

Example

SELECT SQUARE(5); -- Returns 25
SELECT SQUARE(-4); -- Returns 16

SQRT ( Transact-SQL )

Returns the square root of a positive numeric expression.

SQRT ( float_expression )
Parameters
  • float_expression: An expression of the approximate numeric data type category is a valid.
Returns
  • Returnsfloat.

Example

SELECT SQRT(25.00); -- Returns 5.00
SELECT SQRT(100); -- Returns 10.00