SQL Server NTILE Function

Summary: in this tutorial, you will learn how to use the SQL Server NTILE() function to distribute rows of an ordered partition into a specified number of buckets.

Introduction to SQL Server NTILE() function

The SQL Server NTILE() 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. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of the NTILE() function is as follows:

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax in detail:

buckets

The number of buckets into which the rows are divided. The buckets can be an expression or subquery that evaluates to a positive integer. It cannot be a window function.

PARTITION BY clause

The PARTITION BY clause distributes rows of a result set into partitions to which the NTILE() function is applied.

ORDER BY clause

The ORDER BY clause specifies the logical order of rows in each partition to which the NTILE() is applied.

If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY in the OVER() clause.

On the other hand, if the total of rows is divisible by the buckets, the function divides evenly the rows among buckets.

SQL Server NTILE() function illustration

The following statement creates a new table named ntile_demo that stores 10 integers:

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
	
	
SELECT * FROM sales.ntile_demo;
Code language: SQL (Structured Query Language) (sql)

This statement uses the NTILE() function to divide ten rows into three groups:

SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

As clearly shown in the output, the first group has four rows and the other two groups have three rows.

The following statement uses the NTILE() function to distribute rows into five buckets:

SELECT 
	v, 
	NTILE (5) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

As you can see, the output has five groups with the same number of rows in each.

SQL Server NTILE() function examples

Let’s create a view to demonstrate the NTILE() function.

The following statement creates a view that returns the net sales in 2017 by months.

CREATE VIEW sales.vw_netsales_2017 AS
SELECT 
	c.category_name,
	DATENAME(month, o.shipped_date) month, 
	CONVERT(DEC(10, 0), SUM(i.list_price * quantity * (1 - discount))) net_sales
FROM 
	sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN production.products p on p.product_id = i.product_id
INNER JOIN production.categories c on c.category_id = p.category_id
WHERE 
	YEAR(shipped_date) = 2017
GROUP BY
	c.category_name,
	DATENAME(month, o.shipped_date);
Code language: SQL (Structured Query Language) (sql)

SELECT 
    category_name, 
    month,
    net_sales 
FROM 
   sales.vw_netsales_2017 
ORDER BY 
   category_name, 
   net_sales;Code language: CSS (css)

Here is the result:

Using SQL Server NTILE() function over a query result set example

The following example uses the NTILE() function to distribute the months to 4 buckets based on net sales:

WITH cte_by_month AS(
	SELECT
		month, 
		SUM(net_sales) net_sales
	FROM 
		sales.vw_netsales_2017
	GROUP BY 
		month
)
SELECT
	month, 
	FORMAT(net_sales,'C','en-US') net_sales,
	NTILE(4) OVER(
		ORDER BY net_sales DESC
	) net_sales_group
FROM 
	cte_by_month;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Using SQL Server NTILE() function over partitions example

This example uses the NTILE() function to divide the net sales by month into 4 groups for each product category:

SELECT
	category_name,
	month, 
	FORMAT(net_sales,'C','en-US') net_sales,
	NTILE(4) OVER(
		PARTITION BY category_name
		ORDER BY net_sales DESC
	) net_sales_group
FROM 
	sales.vw_netsales_2017;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this tutorial, you have learned how to use the SQL Server NTILE() function to distribute rows of an ordered partition into a specified number of buckets.