Advantages of stored procedures Part 21 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to prism technologies I am
venket this is part 21 of signal server
in this session we'll understand the
advantages of using stored procedures or
ad hoc or inline sequa now before
continuing with this session I strongly
recommend to watch part 18 of this video
series where we have covered the basics
of stored procedures the greatest
benefit of using stored procedures is
that they are capable of reusing an
execution plan okay
let's understand what we mean by an
execution plan okay first of all when we
issue a query to a sequel server three
things happen first it checks the syntax
of that query second it compiles that
query third it generates an execution
plan okay what do we mean by an
execution plan an execution plan is
nothing but for the query to retrieve
the data what is the best possible way
available okay
now this depends on the indexes that are
available in sequel server to help that
query so based on all these factors it's
going to generate an execution plan and
then it executes the query so next time
when you issue the query since that
execution plan is already generated it
will be cached by sequel server so next
time when you run the same query what's
going to happen it's going to reuse that
execution plan okay so basically if it
is stored procedures you know stored
procedures have been doing this from a
long time okay they are capable of
retaining the execution plan caching
that execution plan and reusing that
okay now even with the ad-hoc queries
these days you know they are able to
reuse the execution plan okay with the
new versions of sequel server that we
have but the problem is even a slight
change in the query if it's an ad-hoc
query so even a slight change in the
query can prevent the execution plan
from being reused let's understand what
we mean by that I have the stable call
TBL employee which is very simple it has
got three columns name ID name and
gender and if you look at the query here
it's select name from TBL employee where
ID is equal to one
now when I execute this query what's
going to happen first time when we issue
this query the syntax is checked it is
compiled an execution plan is generated
and then it executes that query returns
the data back which is very good now
when I execute this again the query is
the same thing even ad hoc queries these
days they can you know reuse the
execution plan so it reuses that
execution plan and it would be quick but
on the other hand if I change the
parameter okay
I'm saying in the initial query ID is
equal to 1 now if I pass ID is equal to
2 it is not going to reuse the execution
plan it's going to generate a new plan
for this ok but whereas when it comes to
stored procedures look at this I have
the stored procedure now to the stored
procedure I mean what the stored
procedure basically does is it retrieves
the name of an employee you know for a
given ID so this procedure has a
parameter called @ ID so when you pass
the parameter it gives the name of that
employee ok now if you're new to stored
procedures I strongly recommend to watch
part 18 of this video series where we
have covered the basics of stored
procedures ok so now when I execute this
one look at this the parameter that I am
passing is one so basically the same
thing happens ok
the syntax you know basically the
procedure is compiled and then execution
plan is generated etc etc and then when
I execute this again the execution plan
is reused ok now even if I execute this
even if I change the parameter to two in
case of stored procedures the same plan
will be reused but that's not the case
with ad-hoc sequel queries okay so in
your interview just in case if you are
attending an interview and if you if
somebody asks you you know well stored
procedures can't ad-hoc queries reuse
execution plan they can but even a
slightest change in the query maybe
change in parameter values if it is not
a parameterised query or even a space in
the query you know if for example if you
look at this query you know initially
when I execute this you know the plan
gets created
it I get I execute again the plan will
be reused but even if I have a simple
space here like this and if I execute
the same query again now it doesn't
reuse the execution plan it's going to
generate a new execution plan so a
simple chain you know space or change in
parameter values you know can result in
new execution plan being created okay
but that's not the case with stored
procedures stored procedures can reuse
the execution plan all right
another benefit from a performance
standpoint the stored procedures has is
they can reduce network traffic now how
can they reduce network traffic let's
understand that it's stored procedure
usually something like this we create
the procedure there is the body for the
stored procedure and within the body of
the stored procedure in reality stored
procedures are or you know they've got
enough number of lines maybe there are I
have seen stored procedures even with
three thousand lines of code within them
lots and lots of logic happening within
them okay now when we have such kind of
a stored procedure and we want to
execute that stored procedure all you
have to do is from the client
application it could be a dotnet
application java application or simply a
sequel server management studio so from
the sequel server management studio you
just need to specify the name of the
procedure I mean this execute keyword
the name of the procedure and the
parameter values and then you execute
that okay so over the network what gets
transmitted just this three words okay
but if the stored procedure I mean if
you don't have a stored procedure if it
is just ad hoc sequel that you want to
send from the client tools then you have
to send all those 3,000 lines of code
which is definitely going to have a you
know impact on network traffic
okay so stored procedure since the
stored procedure resides on sequel
server itself all you have to tell the
stories here to do is give the name of
the stored procedure and the parameters
and tell it to execute it that's it but
if it is not a stored procedure then you
will have to pass all those ad hoc
sequel statements
the line tool to the sequel server which
will definitely increase the network
traffic so that's another benefit so
from a performance standpoint I know
these are the two advantages execution
planned reuse and network traffic
redaction okay and another advantage of
using stored procedures is code
reusability and better maintainability
now if it stood procedure you know that
resides on the server now several
applications can reuse that stored
procedure if there are multiple
applications which want a similar kind
of data they can just call that stored
procedure okay now what is the advantage
of doing so the advantage of doing so is
that tomorrow for some reason if there
is a bug in that short procedure or the
logic of the stored procedure has to be
changed you only have one place to
change it irrespective of that being
used by multiple applications so
maintainability also becomes much easier
reusability reusability definitely
promotes maintainability so stored
procedures does that whereas if it is an
inline sequel what's the problem if it's
an inline sequel then every application
has to issue those sequel statements
they prepare that sequel and then issue
those sequel statements from the
application so you have this copies of
sequel statements spread across all the
applications where you want to reuse
that and tomorrow there has to be a
change in the logic you will have to
change it across all those applications
which makes maintenance in nightmare
okay so definitely stored procedures
promote reusability and better
maintainability and from a security
standpoint okay they provide better
security as well okay and they provide
security by two means let's understand
what you know both of them
in terms of finer grained control okay
so what do we mean by that let's say a
user needs data from a specific table
okay now if a user needs let's say I
have IT department HR department payroll
department and one of the managers or
employee needs IT department data okay
now if that table has got all these
departments data ID payroll HR
and if I grant him access to the entire
table what's going to happen is going to
is going to see or everybody's records
and we don't want that okay so in those
cases what you can do you can actually
create a stored procedure and grant him
access to execute that stored procedure
without actually granting access to the
underlying table itself so this give
allows us to have finer grained control
over what data he can see okay so stored
procedures can provide security in that
angle not only stored procedures we can
also use views for that purpose okay but
stored procedures is another way and
another way you know how stored
procedures provide security is they
avoid sequel injection attack now most
of the people who are new to application
development they dynamically build
sequence statements in the application
and then issue those to the to the
sequel server database now what's going
to happen is in the user interface
elements people can type in you know
sequel and inject that to the
dynamically built up sequel code now if
you're if you're if you don't know how
to do that you know I have already
recorded a video on what is sequel
injection attack how keep how can I how
we can actually inject sequel and how to
prevent it using stored procedures I
have a video recorded already on this
please watch this video ok so these are
the these are the greatest benefits of
using stored procedures okay from a
performance standpoint they can reuse
execution plan and reduce network
traffic and code reusability and better
maintainability and from a security
point standpoint you can have finer
grained control by granting access only
to the stored procedure and not to the
underlying tables and they can also
simply avoid sequel injection attack I
mean not only stored procedures even if
it's an ad hoc very if they are
parameterised queries they still can
avoid signal injection attack on this
slide you can find resources for asp.net
and C sharp interview questions that's
it for today thank you for listening
have a great