A recent requirement had me thinking about logic that would help me round any given number to the nearest 100, this would also requires that I actually round to the higher number in the nearest 100. So, if the number was 128 the result should be 200 and not 100. If you use the round function available in SQL there is a simple easy solution.
use madworks
go
declare @num int = round(rand()*1000,0)
select round( @num ,-2), @num
As the screenshot above shows the result doesn’t round to the nearest hundred upper limit. In order to round up always we need to use the CEILING function as shown below. This time is basic math where we divide by 100 and the use ceiling to round up followed up multiplying back with a Hundred to maintain the original number.
USE madworks
GO
DECLARE @num INT = round(rand() * 1000, 0)
SELECT CEILING(@num / 100.0) * 100 AS RoundNum
,@num AS orignNum
The above solution obviously works but it got me thinking, is this the best solution when dealing with millions of rows , would it be faster to calculate the ceiling for each row individually or should be try applying some logic in a set based approach?
In order to check this, I am first trying out a smaller table with 68125 rows as shown below, as you can see from the screenshot the query takes 270ms to complete with hardly any Temp DB usage or CPU overhead.
Next, we use the below query to test out another set-based approach by using spt_values table to generated a static list of 100s tables and then simply use the between operator to check the correct upper limit. In the below screenshot, notice there is tempdb usage as well as COPU overhead about 31 ms however the execution time is 298 ms so it is comparable. We can deduce that for 68K rows the function-based approach make more sense to use due to the little CPU overhead it has.
The results are pretty clear , when you need to calculate the nearest 100s for any given number using the scalar function approach works best.