Important SQL Server Functions – Numeric Functions
May 11, 2021 by Robert Gravelle
Like most modern relational database offerings, SQL Server comes loaded with an impressive collection of built-in functions. While some functions are amazingly similar across the board, exact names and signatures may vary. Therefore, it’s a good idea to brush up on the SQL Server specific implementations of common SQL function. In part 1 of this series, we explored string functions. In today’s installment, we’ll be moving on to numerical functions, a category that is highly useful in the generation of statistics and calculated values!
These are not the Abs that people train to get ready for the beach. Rather, Abs is short for “Absolute”. Hence, the Abs function accepts a numeric value as its argument and returns its absolute equivalent. In simpler terms, Abs returns the positive version of a given number, whether its positive or negative to begin with. Here’s the function signature:
ABS(inputNumber)
In mathematics and statistics, deviation is a measure of difference between the value of a variable and some other value, often that variable’s mean, or average. The deviation can either be signed or unsigned. The latter is where the Abs function comes in. Here’s a query against the ClassicModels Sample Database that shows the signed and unsigned (absolute) deviation of customers’ credit limits, grouped by city:
Another extremely popular numeric function is Round. Rounding functions can vary quite a bit in their implementation; some only round to an integer, while others let you specify the number of decimal places to round to. SQL Server’s Round function goes one step further, by accepting up to three arguments:
ROUND(number, decimals, operation)
- number: a floating-point (decimal) number to be rounded
- decimals: the number of decimal places to round number to
- operation: an optional parameter that affects rounding operations. If 0 (or omitted), the function performs regular rounding, whereby a number of 5 or greater increases the next digit. Any value other than 0, causes the function to truncate the result to the number of decimals.
It’s extremely common to round currency values to 2 decimal places. Here’s our previous query with rounded figures:
The Ceiling function is similar to Round, except that it always rounds up to the next integer value. Hence, both 25.01 and 25.75 would be rounded up to 26. Here’s its syntax:
CEILING(number)
Let’s apply the Ceiling function to our previous query by comparing the credit limits rounded to the nearest integer with those filtered through Ceiling:
Floor is the reverse of the Ceiling function; it always rounds a number down to the first integer that is less than or equal to that number. With positive numbers, Floor simply truncates decimals without altering the next highest integer. However, with negative numbers, it does increment the integer – downwards. For example, the floor of -0.5 is -1, as it is the first integer that is less than -0.5.
FLOOR(number)
Applying the Floor function to our example query without the use of Abs shows its effect on both positive and negative numbers:
In today’s blog, we covered some of the most important numerical functions of SQL Server. In the next installment, we’ll be looking at Date functions.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.