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;

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL