SQL Server NTILE() Function

SQL NTILE() function is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups.

The NTILE() function in SQL server is used to distribute rows of an ordered partition into a specified number of approximately equal groups, or buckets.

It assigns each group a number_expression ranging from one. NTILE() function assigns a number_expression for every row in a group, to which the row belongs. 

The NTILE() function syntax is:

NTILE(number_expression) OVER ( [PARTITION BY partition_expression ] ORDER BY sort_expression [ASC | DESC])

Parameters :

• number_expression The number_expression is the integer into which the rows are divided.
• PARTITION BY clause The PARTITION BY is optional, it differs the rows of a result set into partitions where the NTILE() function is used.
• ORDER BY clause The ORDER BY clause defines the order of rows in each partition where the NTILE() is used.

When a number of rows aren’t divisible by the number_expression, the NTILE() function results the groups of two sizes with a difference by one. The larger groups always come ahead of the smaller group within the order specified by the ORDER BY within the OVER() clause. Also, when the all of rows are divisible by the number_expression, the function divides evenly the rows among number_expression. 

Let’s look at some examples of NTILE() function in SQL Server to understand it better.

First we will create a table named ‘Beginner_demo’

Query:

CREATE TABLE Beginner_demo (
ID INT NOT NULL );
INSERT INTO Beginner_demo(ID)
VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Output:

Use NTILE() function to divide above rows into 3 groups

Query:

SELECT ID,
NTILE (3) OVER (
ORDER BY ID
) Group_number
FROM Beginner_demo; 

Output :

Use the NTILE() function to distribute rows into 5 groups

Query:

SELECT ID,
NTILE (5) OVER (
ORDER BY ID
) Group_number

FROM Beginner_demo; 

Output :

Using the NTILE() function without number_expression Example

SELECT ID,
NTILE () OVER (
ORDER BY ID
) Group_number

FROM Beginner_demo; 

Output: It will throw the below error:

The function 'NTILE' takes exactly 1 argument(s). 

• The NTILE() function is a window function that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets.
• It assigns each group a bucket number starting from one.
• If the total of rows is divisible by the buckets, the function divides evenly the rows among buckets.
• If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one.
• Proper indexing on columns used in the ORDER BY clause within the OVER() partition can improve performance when using NTILE() on large datasets
• It is useful for creating histograms, analyzing rankings, or dividing data to allow parallel processing.