Offset fetch next in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 130 of sequence of a
tutorial in this video we'll discuss
offset fetch clause in sequel server
offset fetch Clause makes it very easy
to implement paging this Clause is
introduced in sequence over 2012 it
returns a page of results from the
result set order by Clause is required
here is the syntax select star from your
table name order by the column list by
which you want to sort the data offset
and then the number of rules that you
want to skip for example if you want to
skip first ten rows then you specify 10
as the value for rows to skip and then
you use the rows keyword followed by
that fetch next and the number of rows
that you want to fetch for example if
you want to fetch the next set of 10
rows then you will specify 10 as the
value for roast of it and then you use
rules only keyword this syntax is very
readable look at this select star from
your table name order by column list
offset the number of rows you want to
skip fetch next the number of rows that
you want to fetch let's use this table
on the right here for the examples in
this demo notice this table has got four
columns ID name description and price at
the moment in the image we only see ten
rows but in the table that I have here
that are actually hundred rows so as I
scroll down notice we have hundred rows
in the table and here is the sequel
script to create the table and this
script will populate the data within
that table now if you look at the data
that we have here look at the names
product one product to product three so
on and so forth and look at the
description product description one
product description two product
description three so on and so forth and
look at the price ten twenty thirty etc
now the data is actually computed by
using this while loop right here so
let's quickly look at this script so
here I have a variable at start of type
integer we have initialized that value
of one and then I have two variables
here name and description and then here
we have our while loop at start less
than or equal to 100 that's our boolean
expression at the moment start value is
1 so 1 is less than or equal to 100 that
evaluates to true so it comes inside the
loop and then computes the value for
name vary
name equals product - and whatever value
we have within the start variable at the
moment the value within the start
variable is one so product - one will be
the value within this named variable
similarly description we'll have product
description - one and then we are using
those variables within this insert query
right here insert into t-bill products
values the first variable value will be
for the name column the second variable
value will be for the description column
and then we have the value for price
column here look at what we are doing we
are multiplying the start variable by
ten at the moment start variable value
is 1 1 multiplied by 10 is 10 so for the
first row within the table product name
will be product - 1 product description
will be product description - 1 and the
price will be 10 okay and then we are
incrementing the value of start variable
by 1 and then it inserts the data for
the second row so this goes on until you
know start variable value is 1 0 1 when
the value become 1 0 1 at that point
this condition becomes false and it will
break out of the loop
that's how we ended with hundred rows in
this table
I'll have the script available on my
blog in case you need it alright now
what we want to do is using this offset
fetch clause that's introduced in sequel
server 2012 we want to skip the first 10
rows and then fetch the next set of 10
rows okay so select star from your table
name remember with offset which Clause
order by Clause is required order by
let's sort the data by ID column and
then we use the offset keyword and the
number of rules that you want to skip so
we want to skip 10 rows and then we use
fetch next and the number of rows that
we want to fit so let's fetch the next
set of 10 rows and then we use rows only
key word so when we execute this it
should skip the first set of 10 rows and
retrieve the next set of 10 rows when
the data is sorted by ID column so that
means we should get rows 11 to 20
so let's look at that notice first of
all we get 10 rows and look at the ID
column we get from ID 11 to 20 as
expected now for some reason if you want
to skip the first set of 20 rows and
then retrieve the next set of 10 rows
you can simply change the number of rows
that you want to skip so when we execute
this we should get rows from 21 to 30
for some reason if you want to get the
next set of 30 rows you simply change
that value to 30 so now we should get
rows from 21 to 50 so it's that easy
implementing paging using offset fetch
Clause in sequence over 2012 and that we
have that same example right here
typically in a real world you know from
a front-end application where you are
implementing paging be it asp.net
webforms application or windows forms
application so from the front-end
applications you would typically send a
page number and page size right to the
server side store procedure and that's
stored procedure depending on the values
for page number and page size it should
return the correct set of rows so let's
actually come up with the stored
procedure that can actually do that I'm
actually going to convert this code into
a stored procedure so let's create a
procedure let's call this SP get rows by
page number and page size and the stored
procedure is going to have two
parameters at page number type is going
to be integer at page size again the
data type is going to be integer as
begin and let's end up a store procedure
right there okay
so select star from TBL products order
by ID column offset at the moment we are
hard-coding the number of rows that we
want to skip instead of that I'm going
to use the values of these 2 parameters
to compute the value for rules that we
want to skip so here I'm going to use
page number parameter so at page number
I'm going to subtract one from that and
whatever value we get we are going to
multiply that by page size parameter
value okay so those will be the number
of rules that we want to skip and we
want to say you know the next set of ten
rows you know or whatever value that we
have within this parameter okay
so let's understand this so for example
if we pass page number as one that means
the end user wants to see the first page
of data and let's say the page size that
the user has selected on the front end
is ten that means page number will be
one paid size will be ten so that means
we don't want to skip any rows we want
to skip zero rows so this expression
should evaluate to zero page number is 1
1 minus 1 is 0 0 multiplied by page size
page size is 10 so 0 multiplied by 10 is
0 so skip 0 rows and then fetch next you
know what value we have in paid size
parameter 10 so it's going to fit the
next set of 10 rows so when the page
number becomes 2 so if the user wants to
see what data we have on page number 2
so page number will be 2 2 minus 1 is 1
1 multiplied by paid size that is 10 so
1 multiplied by 10 is 10 so we want to
skip 10 rows and then retrieve the next
set of 10 rows so this stored procedure
should give us the data that we expect
so let's go ahead and create this
procedure now if we pass so let's copy
this to a procedure and execute it so
execute that's our procedure the value
for page number is going to be 1 and
page size is going to be then that means
we should get rows from 1 to 10 notice
that we get rows from 1 to 10 if I say I
want 2nd page data now we should get
rows from 11 to 20 if we supply a value
of 3 for page number then we should get
rows from 21 to 30 so look at the stored
procedure how simple it is
using offset which clause in sequel
server we have the exact stem stroke
seizure right here thank you for
listening and have a great day