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.