Cursors in sql server Part 63 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to prism technologies I am
venket this is part 63 of sequel server
video series in this video we'll discuss
about curses in sequel server relational
database management systems including
sequel server are very good at handling
data in SATs for example consider the
subject statement update TBL product
sales set unit price is equal to 50
where product ID is equal to 1 0 1 all
the rules in TBL products sales table
which meet the condition in the where
clause will have the unit price updated
to 50 as a single set and sequel server
is very efficient at operating on data
in sets in fact you know sequel
statements like update delete select etc
they handle data in sets in a very
efficient way but however if there is
ever a need for us to process the rules
on a row by row basis then cursors can
be used cursors are very bad for
performance and they should always be
avoided since cursors operate on a row
by row basis they can be extremely slow
but remember most of the time these
cursors can be very easily replaced
using joins and in sequel server there
are different types of cursors for word
only static key set dynamic etc we'll
talk about the differences between these
cursor types in a later video session
let's look at a simple example of using
cursors now before we look at cursors
let's understand what a cursor is a
cursor is nothing more than a pointer to
a row now let's say when I execute a
select statement I have this result set
and I want to process the rows within
this result set on a row by row basis
I want to process individual row at a
time okay so if that's the case I can
have a pointer or a cursor pointing to
this result set you know maybe the
cursor is pointing to the first row and
if I if I ask it to give the row it
gives me the first row and then it moves
to the second row and then when I ask it
to give the row again it's going to get
that row and move to the next row at
some point of time when it passes you
know when the cursor returns the tenth
record there are no more rows within
this result set in which case it doesn't
return anything back
okay so let's see how to use cursors now
what I basically want to do is using
cursors I want to print the ID and the
name of each row that is present in TBL
products table okay so I have two
variables to hold the ID and the name
I'd product IDs of type integer name is
of type and where Cal and then look at
this this is the you know statement
where we are declaring occurs are using
the declare keyword declare name of the
cursor and then cursor for okay we will
have a cursor for a result set and to
get the result set we use a select query
so if I execute the Select query I'm
going to get all the products where ID
is less than or equal to 1000 just to
make this query run a little faster I
have only retrieved 1000 records
otherwise it's going to take a little
bit of time okay so this select
statement is giving me a result set and
I am asking a pointer or cursor to be
pointing to that result said okay so
declare name of the cursor cursor for
and you specify a select statement okay
so we have declared a cursor at this
point okay now when you say open product
cursor which is nothing but this cursor
that we have just declared what's going
to happen the Select statement will be
executed and all the rules that match
that select statement are the conditions
in the where clause are now retrieved
into this result set and you will have
your cursor pointing just above the
first record so the cursor will not be
pointing to any row okay it will be
pointing you know it will be ready to
retrieve the rows okay now when you say
fetch next from product cursor into
product ID and name what's going to
happen the cursor will retrieve the
first record the ID into product ID
column and the name into the name column
I mean a name variable so we have you
know the data of the first row retrieved
into these variables and then look at
this we are checking okay while at at
fetch status is equal to 0 now this
variable is going to return 0 as long as
we have rows you know within the
said okay remember the cursor will
return one row at a time so it returns
the first row then it goes to the second
row then the third row etc now when it
has passed all the thousand rows then it
this netted fit status will not be zero
that's the indication for that for us
you know to know that we have processed
all the rows so the switch status will
return zero as long as there are rules
to be processed and now what I want to
do I want to print the ID so print let's
say ID is equal to whatever ID we get so
where is the ID present ID is present in
the set product ID variable so I'm going
to cast that to be of type and we're
care because we are printing you know
the product ID and we are concatenated
that to string so we need to typecast
that and going to say you know the
length s 10 okay and then maybe I want
to say maybe name is equal to and where
is name present that's present in this
ad name variable and look at this so we
are printing the ID n name and what do
we have huh gain here fetch next from
product cursor into that variable so we
have got the first row here now we are
retrieving the second row and we are
going to do this as long as there are
rows within our result set because we
are present these statements are present
inside a loop okay now when the cursor
reaches the thousand record there are no
more rows
you know so the ad fetch status will
return nonzero and that's the indication
for us we have complete processing the
rows in the result set and that finally
I have closed product cursor so what is
this going to do it's going to release
the results at this statement is going
to release the results head and finally
deallocate will actually deallocate the
resources that are you know being used
by this cursor okay these two statements
are very important to close the result
set and to deallocate the resources that
are used by the cursor now let's go
ahead and run this and see what's going
to happen so as expected it should print
the ID and the name of the product so
see how easy it is to actually look
through the rows using curses now let's
look at a simple example of
using cursors now let's say for example
I want if you look at this TBL product
sales table it has got you know the
product ID and the unit price quantity
etc now let's say I want to update this
unit price and there are some conditions
to follow to update the unit price in
TBL product sales table now if the
product name is product 55 then I want
to set the unit price in this table to
55 but whereas if the product name is 65
then I want to set the unit price to 65
but whereas if the product name you know
starts with product - 100 after that I
don't care what we have but in that case
if a product name is like that product -
100 and then anything after that then I
want to set the unit price to 1000 okay
so I mean this is these are some crazy
conditions but I want to demonstrate the
use of cursors here let's see how to do
that and one important thing to keep in
mind is we don't have product name in
this table but remember if the product
name is 55 then I want to set the unit
price to 55 if the product name is 65
then I want to set the unit price to 65
whereas if the product name is like you
know if it starts with product - 100
then I want to set the unit price to
1000 okay there are some conditions and
we don't have product name in the stable
products sales table
so what we will have to do will create a
cursor for this table okay for this
result set for all the rules within TBL
product sell sales and then we will loop
through Ichiro retrieve the product ID
go to the products table and then
retrieve the name check if the name is
you know 65 55 then update the unit
price to 55 otherwise 65 otherwise
thousand ok let's see how to do that
okay so I'm going to call that product
cursor I don't need you know I need a
name variable but I don't need the name
here so I want to retrieve select ID
from not ID it's actually product ID
because in DB
products sales table look at this
integral product sales we want to
retrieve the Product ID so select
Product ID from TBL product sales table
okay I'm going to get rid of this where
clause there and I want to retrieve you
know when I retrieve this result set I
am creating a cursor open product cursor
and fetch next from product cursor into
product ID there is no name within the
result set so I just have the product ID
so once I have the product ID then what
we want to do we basically want to
retrieve the name of the product look at
this is the same code we are declaring
two variables we are declaring a cursor
and we are retrieving the product ID
from product sales table and we are
opening the product cursor where we are
retrieving the first row into that
variable the product ID into the first
the product ID of the first row into
this variable and while fetch status is
0 what do we want to do we want to
retrieve the name of the product so I am
going to copy this piece of code and
paste it there
okay so what are we doing here we
already have a variable for the product
name so I'm going to get rid of that I'm
going to call instead of simply name I'm
going to call this product name okay so
we are retrieving the product name from
TBL products table using the ID that we
have caught into this variable okay and
then what we are doing we are checking
if the name is product 55 then we are
updating TBL product sales table to set
the unit price to 55 where product ID is
equal to whatever is the product ID that
we have retrieved from pbl product sales
table and the most important thing is
now and we are doing that for product 65
as well if it's 65 then we are setting
the unit price to 65 whereas if the name
starts with product - hundred we are
setting the unit price to 1000 and
finally I want to retrieve the next row
I just deleted this by mistake so fetch
next from product cursor into product ID
and this will be repeated as long as we
have rules in TBL product sales table
okay so let's go ahead and run this and
see how long is this going to take and
remember they're around six six hundred
thousand records in my table so it's
processing processing look at that it's
still executing the query it's going to
take a little bit of time because how is
this query executed now it's going to go
to the product sales table look at this
it's still executing it's already 14
seconds and still executing let's
understand how this query is being
executed go to the product sales table
retrieve the product ID and then go to
the TBL products table get the name and
then compare that you know with 55 if it
is 55 then it updates the unit price to
55 otherwise 65 otherwise whatever
thousand so this is going to do all this
processing
600,000 times because there are 600
thousand records in TBL product sales
table and the query has just completed
now look at this it took around 43
seconds since it has to process the rows
on a row by row basis it took around 43
seconds but we can very easily replace
this cursors query using joins and we
discuss about how to do that in our next
video session and you know when you
replace curses using joins it's going to
improve the performance significantly
you don't even you know you will you
will be surprised that the update query
these the equivalent update query is
just going to take less than a second if
you use joints okay so let's actually
see if the update has worked as expected
and to do that I already have a set I
already have a select query here so I'm
selecting the name and unit price by
joining TBL products and TBL product
sales and I have set name is equal to in
the where clause you know name is equal
to product 55 product 65 or thousand so
obviously when we execute the Select
query we should see look at that any
product that has 100 that the name that
starts with product - 100 the unit price
is thousand if you want to see for
product 55 and 65 I'm just going to
execute that query and look at that
product 55 then try this 55 and for
product 65 it is 65 okay so the cursor
will loop through each row in TBL
product sales table as there are 600
thousand rows to be processed on a row
by row basis it took around 43 seconds
on my machine but we can achieve this
very easily using a join and this will
significantly increase the performance
we'll discuss about this in our next
video session on this slide you can find
resources for asp.net c-sharp and sequel
server interview questions that's it for
today thank you for listening have a
great day