Here is a simple and practical use-case of the NTILE function. We’ve used it to divide the rows of sys.columns into N groups. N is the number of groups. We’ve used the number of groups as 7. So the entire rows will be aligned equally to any of the group between 1 to 7.
When we say equally, it doesn’t mean all the individual groups will have the equal number of rows. Allocation will be done equally, but the number of rows in each group depends upon the total number of rows. If the total number of rows is divisible by the number of groups then all the individual groups will have the same record count. But if the total number of rows is not divisible by the number of groups then not all groups will have the same record count.
/*
Assign the dynamic number of groups here. It can also be a parameter of the procedure / function.
*/
DECLARE @Number_Of_Groups TINYINT = 7
; WITH cte_staging
AS
(
SELECT *
, NTILE(@Number_Of_Groups) OVER(ORDER BY [column_id] ASC) AS GroupID
FROM sys.columns
)
SELECT GroupID
, COUNT(1) AS TotalRows
FROM cte_staging
GROUP BY GroupID
ORDER BY 1 ASC
The output will look like as can be seen in the image below. You’ll notice the total rows of Group ID’s 5, 6, and 7 is 164, whereas it is 165 for the other Group ID’s. There are total 1152 rows in the sys.columns table. 1152 is not divisible by 7.
Let us now try to run the same query with a different number of groups. We’ll now consider the number of groups as 8. This time we’ll find same total rows in all the groups as can be seen in the image below. In this case 1152 is divisible by 8. You can say that each group is of the equal batch size of 144.
SQL Server Carpenter 
Instead of “batch size”, you should probably use the phrase “group number” or “batch number”. When I read your article I thought you were indicating the size of each batch when, in fact, you were meaning the number of batches. I came up with “group number” because that is the term used by Microsoft in their NTILE() online docs.
https://docs.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql?view=sql-server-ver15
LikeLike
Also, just because 1152 is an even number and 7 is an odd number it cannot be concluded that 1152 cannot be evenly divided by 7. For example, 42 is an even number but it can be evenly divided by 7 (result = 6). Likewise, just because both 1152 and 8 are even numbers it doesn’t mean that 8 can evenly go into 1152. For example, 1152 and 10 are both even numbers. If you made the group number equal to 10, each batch would not have the same record count.
LikeLike
That’s a perfect catch Larry! I really appreciate your observation!
I now realized I made a very basic mistake in the process of explaining the working.
I would like to take this opportunity to correct it for the other readers.
“Each batch/group number will have the exact same record count if the total number of rows is divisible by the batch/group count. Otherwise, not all groups/batches will have the same record count.”
I’ve also modified the article to accommodate your feedback and to avoid the anomaly.
Thank you very much!
LikeLiked by 1 person
Excellent work. I am wondering if you may be able to help me with an issue? Is it possible to list, in equal batches just order numbers for a given day? For example, normally in a query you would see a vertical column of ord_no like 493009, 493010, 493031, 493035, 493038, 493040 etc. I have been attempting to list them as a grid with 5 by whatever total number of order numbers (could be any where from 14 to 100). I’ve tried using
SELECT NTILE(5) OVER(
ORDER BY t0.ord_no) AS DisplayColumn,
t0.ord_no
FROM oehdrhst_sql AS t0
WHERE posted_dt >= DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(day, -1, GETDATE())))
AND t0.ship_via_cd 14
AND NOT(t0.user_def_fld_3 IS NULL)
AND ((user_def_fld_4 NOT LIKE ‘RMA#%’)
OR (user_def_fld_4 IS NULL))
AND ord_no NOT IN
(
SELECT OrderNumber
FROM dbo.ShipNotificationsSent
)
but keep coming up with everything in two columns rather than a “grid like” display. We do have a C# app that developed by an inhouse programmer but are trying to get away from legagy apps. What may I be missing?
Thank you.
LikeLiked by 2 people
Hi William,
I believe Pivot should help you. Check out the following article. It talks about similar stuff.
Thank you!
LikeLiked by 1 person
Hi William,
I got some free time today and had a close look at your requirement. I found PIVOT may not help in this case.
I spent some time on your requirement and came up with a script. It will surely serve your purpose and is completely dynamic.
I tried to post the script here in the comment several times, but I observed code loss. Few lines of code were getting disturbed, causing the error while executing the script copied from the comment. That is why I thought to write a new article for the same.
You can refer to the following article for the script.
Thanks,
Brahma.
LikeLiked by 1 person