NTILE function in SQL Server

Written on May 20, 2016

NTILE is one of ranking function in SQL Server. NTILE function is used in scenarios where we have requirement to divide result set in to some quarters, and with each row we want to get that quarter's number. We might think of real life situation such as suppose we have to get email addresses for campaigning from database. And we also want that these returned emails are divided in four groups.

Syntax for NTILE is as follows.

NTILE (no_of_quarter) OVER ( [  ] < order_by_clause > )

Let's say we want to get some products in inventory with their quantity. And we also want these result set to be divided into four quarters. Its query will be as below.

How NTILE divides result set?

If total rows are not divisible by number of quarters specified, then we have different sized groups. In above example, we have total 10 rows and 4 divisions. As a result first two divisions has 3 rows each and last two divisions have 2 rows each. Now if we re-run these query with providing two quarter, then we have evenly distributed groups with 5 rows each.

For more granule partition within each group we can use NTILE with PARITTION BY statement.