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