Here is the simple and practical use case of NTILE function. We’ve used it to divide the rows of sys.columns into N batches. N is the batch size. We’ve used batch size as 7. So the entire rows will be aligned equally to any of the batch between 1 to 7.
When we say equally, it doesn’t mean all the individual batches will have the equal number of rows. Allocation will be done equally, but the number of rows in each batch depends upon the total number of rows. If the total number of rows is divisible by the batch size then all the individual batches will have the same number of rows. But if the total number of rows is not divisible by the batch size then few of the individual batches will have less number of rows.
/*
Assign the dynamic batch size here. It can also be a parameter of the procedure / function.
*/DECLARE @Number_Of_BatchesTINYINT=7
; WITH cte_staging
AS
(
SELECT *
, NTILE(@Number_Of_Batches) OVER(ORDER BY [column_id] ASC) AS BatchID
FROM sys.columns
)
SELECT BatchID
, COUNT(1) AS TotalRows
FROM cte_staging
GROUP BY BatchID
ORDER BY 1 ASC
The output will look like as can be seen in the image below. There are total 1152 rows in the sys.columns table when the query was ran. 1152 (total rows) is an even number, whereas 7 (batch size) is an odd number. Hence it is not divisible and that is why you can see few batches with less number of rows.
Let us now try to run the same query with a different batch size. This time we’ll consider an even batch size of 8. Now both the numbers i.e. total rows (1152) and batch size (8) are even numbers. This time we’ll find same number of rows in each individual batch as can be seen in the image below.
