Modulo!
7April 14, 2016 by Kenneth Fisher
I love this somewhat obscure mathematical operator. All it does is return the remainder of a division statement but even though it’s pretty basic most people I talk to have never heard of it. The format is
dividend % divisor = remainder
DECLARE @remainder INT DECLARE @dividend INT = 10 DECLARE @divisor INT = 6 SET @remainder = @dividend % @divisor -- @remainder = 4
Now aside from the odd occasion when you actually need it for it’s simple purpose it’s a rather interesting way to get a rolling count. Basically you can use it to get a list back of 1,2,3,…n-1,0 where n is your divisor.
DECLARE @divisor INT = 4; SELECT -- Row Number ROW_NUMBER() OVER (ORDER BY object_id), -- Default 1,2,...n-1,0 ROW_NUMBER() OVER (ORDER BY object_id) % @divisor, -- More useful 1,2,...n ((ROW_NUMBER() OVER (ORDER BY object_id)-1) % @divisor)+1 FROM sys.objects -- Skip to the 3rd "page" to make this more obvious ORDER BY object_id OFFSET @divisor * 2 ROWS;
Now this can have all kinds of interesting uses. Particularly when you use the slightly modified third column to get a list that’s 1,2,…n.
Another common use for modulo (I’m not going to make any assumptions about it’s efficiency) is to get every xth row of a result set.
DECLARE @x INT = 4; WITH MyCTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS Row_Number, * FROM sys.objects) SELECT * FROM MyCTE WHERE Row_Number % @x = 0;
SQL Studies 
Interview Tip: This is how you calculate change from a vending machine or the fewest stamps to put on an envelope!
Modulo is a great way to split a single result set into multiple chunks to speed up bulk loads. We use it in SSIS to take a single data flow and turn it into 2, 4 or even 8 parallel streams; it’s like putting afterburners or nitrous oxide on your package.
I hadn’t thought of using it like that in SSIS but makes sense. Very cool.
This is a great post. I’ve used Modulo a lot over my programming lifetime and you are right, it’s often overlooked but a lot better than how I used to do it in BASIC which was something like if int(x/100) = x/100.
Glad you liked it. Yea, Modulo is one of my favorite “math” functions. If only because it’s so unusual.
[…] % (modulo) 1000: This returns the remainder of our random value divided by 1000. Basically this creates a range of values. In this case -1000 to 1000. If you want a range of -5 to 5 then use % 5. Simple enough. […]
[…] I needed to determine what should be printed out. Well modulo and a case statement is prefect for that. A simple case statement checking for Id % 3 = 0, Id % 5 = […]