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
-- Skip to the 3rd "page" to make this more obvious
ORDER BY object_id
OFFSET @divisor * 2 ROWS;

modulo1

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.

  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.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: