# Modulo!

7

April 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
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;```

## 7 thoughts on “Modulo!”

1. notarian says:

Interview Tip: This is how you calculate change from a vending machine or the fewest stamps to put on an envelope!

2. Gabriel says:

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.

3. dewitte says:

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.

4. […] % (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. […]

5. […] 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 = […]

Join 1,664 other followers