SQL Lion

NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  ROW_NUMBER()
  RANK()
  DENSE_RANK()

Let’s consider we have a table with 1 million records, and we want to divide this 1 million records across five tables, then we can achieve this by using NTILE () function.

The functionality of NTILE () function is same as other ranking functions i.e. ROW_NUMBER(), RANK() and DENSE_RANK(), except it takes one parameter named “integer_expression” i.e. NTILE(integer_expression). The integer_expression signifies the number of groups the NTILE function will create to distribute the data. integer_expression can be of type int, or bigint but must be positive integer. If you specify any other value like 2.5 or -3, it will throw the below error:

The function ‘ntile’ takes only a positive int or bigint expression as its input.

Let’s consider the example of Employee table, where we have 7 records, and our purpose is to distribute these records into two separate tables, one containing highest salaries and the other with lower salaries. Then the query goes like this:

For the table with Highest Salaries:

select * from (
select NTILE(2) OVER( order by salary desc) as GroupID,* from dbo.employee
) InnQ
where InnQ.GroupID = 1

OUTPUT:

For the table with Lowest Salaries:

select * from (
select NTILE(2) OVER( order by salary desc) as GroupID,* from dbo.employee
) InnQ
where InnQ.GroupID = 2

You might have noticed here that in the first set we have four records while in the second one we have three records. This is because; we have odd number of records in the employee table from where these sets are derived. When the integer_expression parameter is divisible with the total number of records in the source table, then we can get even number of records in all the sets derived from it or else it will start filling the remaining records starting from first set.

Let’s dissect the query a little:

select NTILE(2) OVER( order by salary desc) as GroupID,* from dbo.employee;

GroupID column has two distinct numbers on the basis of which the records are divided into two separate sets as shown below.

As the total number of records in the employee table is not divisible by the integer_expression parameter in NTILE (), we have two groups with different sizes differ by one member. The group with higher number of members comes before the group with small number of members.

Using <Partition By> in NTILE():

<Partition By> clause in NTILE () function increases it functionality a little more. Here, we will see how partition by affects NTILE () in the above example. Let’s consider the below query to group records by department wise.

select NTILE(2) OVER(PARTITION BY DeptNo order by salary desc) as GroupID,* from dbo.employee;

OUTPUT:

Let’s dissect the above query and see the process behind it.

The OVER clause in the query will partition the employee table as per DeptNo i.e. into three sub-sets. Then the NTILE(2) will divide each partition into groups of two as show in the below figure.

Hope this article may come handy in T-SQL programming. Waiting for your valuable comments. Happy programming

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  ROW_NUMBER()
  RANK()
  DENSE_RANK()