NTILE function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 113 of sequel server
tutorial in this video well discus
entire function in sequel server this
function is introduced in sequel server
2005 order by clause is required
partition by Clause is optional this
function distributes the rows into a
specified number of groups if the number
of rows is not divisible by a number of
groups then you may have groups of two
different sizes larger groups come
before smaller groups for example if you
have ten rows in a table and you want to
distribute those ten rows into two
groups then you specify n tile of 2 so 2
here is the number of groups that you
want so we will have two groups with
five rows in each group and tile of
three on the same set of ten rows will
distribute those rows into three groups
for in the first group and three rows in
the second and third group we have the
syntax for this function here so we
specify the name of the function and
then this function has got one parameter
that is the number of groups that we
want followed by that over keyword and
then within parenthesis we use the order
by clause and then the column list by
which we want to sort the data let's
look at an example now let's use this
employees table in this example now what
we want to do is distribute these ten
rules into two groups and with no result
set we want name gender salary columns
so select name gender salary and then we
are going to use the entire function
this function has got a single parameter
that is the number of groups that we
want we want two groups and followed by
this we use the over keyword and then
within Francis we use the order by
clause and then the column by which we
want to sort the data let's sort the
data by salary column in ascending order
and let's give this column Analia as
let's call this n tile since n tile is a
function name let's wrap this with a
pair of square brackets so let's execute
this query and see what we get now
notice we've got these ten rows and look
at what happened to the first set of
five rows they are in Group one
and the second set of five rows are in
group two here the number of rows are
divisible by the number of groups we
have ten rows and the number of groups
that we want is two so 10 divided by 2
is 5 so each group has 5 rows with a net
now let's say we want to distribute
these 10 rows into 3 groups in that case
we specify 3 as the parameter value for
n tile function so when we divide 3 by I
mean 10 by 3 we don't get you know we
can't evenly distribute those rows so
what's going to happen is the first
group will have 4 rows and the rest of
the two groups will have 3 rows in each
so 2 rows into 3 groups it's 6 and the
first group has 4 rows so 6 plus 4 is 10
so let's execute this query and see what
we get look at that the first group has
got 4 rows and the second group has got
3 rows and the last group also 3 rows so
if the larger groups always comes before
the smaller groups when the rows are not
distributed equally among the groups now
what happens if the specified number of
groups is greater than the number of
rows now in this case n tile function
tries to create as many groups as
possible with one row in each group now
if you look at this table we've got 10
rows with a net but if I try to create
11 rows what is going to happen we don't
have enough data for 11 rows so it's
going to put one row with an H group and
since it doesn't have any more data to
create an 11th group it will not create
that 11th group but then when we execute
this notice that within each group we
have wanderer so further the examples we
haven't used partitions what happens if
there are partitions involved let's
partition this data by gender column so
let's use the partition by Clause
now we have two different genders male
and female so the data will be
partitioned into two partitions and
let's say we want three groups so these
three groups will be created for each
partition let's actually execute this
and see what we get
look at that first we have female
partition and it has divided the rows
into three groups okay so the first two
female rows are in one group the second
row is present in the second group and
the third row is present in the third
group and look at the male employees
group again these roles are distributed
into three groups since we have six rows
in this partition each group has got two
rows and we have that example right here
thank you for listening and have a great
day