Important concepts related to functions in sql server Part 33 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to prism technologies I am
venket this is part 33 of signal server
in this session we'll learn about some
of the important concepts related to
functions specifically we'll be looking
at deterministic and non-deterministic
functions how to encrypt and scheme
abuyin functions before continuing with
the session I strongly recommend to
watch parts 30 31 and 32 of this video
series now what are deterministic and
non-deterministic functions and what are
the differences between them this is a
very commonly asked interview question
as well now let's understand these with
an example now if you look at this
employee's table I've got five rows in
this table now look at this when I
execute this select count of star from
TBL employees count function returns the
total number of records in the table
employees okay there are five records so
the function returns five so no matter
how many times I execute this query you
know it returns five to me okay now if I
change the database state in the sense
if I insert new records or delete the
existing records and try to select count
you know that's when it changes for
example if I delete one row and try to
execute select count of star that's
going to give me four records but given
that the database state has not changed
and I haven't changed the input
parameters of this function you know
it's going to give me the same result so
we can determine the same result okay
let's look at the next other example for
example we have a function called Squire
function okay which will Squire the
number that we provide to it for example
if I pass in three and I execute this
query look at this every time you
execute this it gives you nine okay
every time you execute that it gives you
nine so you can determine the result of
this function so let's look at the
definition of deterministic function a
deterministic function always returns
the same result anytime they are called
with a specific set of input values and
given the same state of the database
okay so here we have not changed the
input value so when we execute this
function I
the same result no matter how many times
they execute that okay and along the
same lines if you look at the count
function this is going to give me the
total number of records unless I change
the state of the database in the sense I
either delete or insert new records you
know the count value is going to change
otherwise it's not going to change in
any way so deterministic functions
always return the same result anytime
they're called
with a specific set of input values and
given the same state of the database and
examples Squier power some average and
count in fact all aggregate functions
are deterministic functions okay
now let's look at non deterministic
function obviously non deterministic
functions you know are opposite to
deterministic functions for
deterministic functions they always
return the same result for the given
state and for the given set of input
values whereas non deterministic
functions may return different values
for the same set of specific input
values and even if the database state
has not changed okay the classic example
is the get date function okay no we
didn't change any records in the table
we also didn't change the input
parameters get date function doesn't
take any parameters okay so now if I
execute this function I get some time so
two eight is twenty five thirty three
twenty-five seconds now when I execute
that thirty three thirty seconds okay so
every time you execute that the time is
going to change for the same set of
input values in fact here this function
doesn't take any input parameters and
and even if the database state has not
changed I'm gonna get different result
every time I execute this function okay
so that's an example of
non-deterministic function even current
timestamp even current timestamp is
going to return the current date and
time on the sequel server so obviously
even this is non-deterministic and there
is a function called rand function we
have spoke about spoken about this as
well in the previous sessions of this
video series okay we use a random
function to get a random number and we
know that this function takes a
parameter called
seed okay now let's look at this
function because a random function can
act as deterministic function at times
and at times it can become non
deterministic function now look at this
when I say select R and this function is
expecting an integer parameter seed
value now if I pass let's say seed 1 and
when I execute this function look at
this I get this value I execute this
again I get the same value so for the
given seed no matter how many times you
execute this rand function you get the
same number so at this point this is a
deterministic function if you provide
the seed value the function is
deterministic on the other hand if I
remove the seed value and try to execute
this every time I execute I get a
different result look at that okay
so if the seed value is not provided
random is non-deterministic function
okay so Rand function can behave as both
deterministic and non-deterministic
depending on how you execute that if you
execute it with the same seed value then
it's going to be deterministic function
otherwise it's non deterministic in
nature
all right the other two important
concepts that we'll talk about is
encrypting the function definition and
schema binding okay encrypting the
function definition is the same as
encrypting the stored procedure
definition they spoke about stood
procedures in part 18 of this video
series and we have seen how to encrypt a
stored procedure along the same lines we
encrypt a function as well let's look at
an example so let's say I have this
table called TBL employees now what I
want to do is I want to create a simple
function which gets the name of the
employee given the ID again if you're
new to functions I told you please refer
to parts 30 31 and 32 of this video
series we have discussed about them
extensively there so let's create this
function a scalar function that's going
to return the name of the employee
provided we give it the ID so create
function FN underscore let's call this
get name by ID and let's pass in the ID
parameter
of type integer and this function is
going to return and work our because we
want the name back so and we're care of
30 as begin and so what we want we want
to select the name of the person so we
will say select name from TBL employee's
table where ID is equal to whatever
aidid we are passing into this function
which is nothing but a tidy all right so
let's create this function out in
correct syntax returns
instead of return so let's execute that
oh we need to have a return keyword here
because we want to return the name of
the person execute that ok the function
completed successfully now obviously in
order to execute this function we
already know that select dbo we have to
give it the two path name again we spoke
about this in detail when we spoke about
scalar functions in sequel server so
let's say when I pass in ID 1 so
obviously if you look at TBL employees
table ID 1 is Sam so we get Sam back ok
fine
now if I want to look let's say I have
deleted this text from here now I want
to look at this FN get name by ID
function maybe I want to alter that or
for some other reason I want to look at
the definition or the implementation of
this function how do I do that there are
two ways one is you can actually use a
system stored procedure called SP
underscore help text to get the
definition of this function so SP
underscore help text and then provide
the name of the function whose text you
want when you press f5 you should get
the definition of the function that's
one way another way is if you go to the
functions folder within the database and
then expand since this is a scalar
function if you expand scalar functions
folder you should see you a function
there when you right-click on that
script function as altered to new query
editor window you should
get that function implementation there
okay that's another way all right
so I get this function so when I use
this SP underscore help text system
stored procedure I am able to retrieve
the text of this particular function now
let's say we don't want to we don't want
to allow the users to these two see the
text of our function our stored
procedure can be encrypt absolutely you
can encrypt and it's very easy to do
okay so let's copy-paste the
implementation of the function all you
do is you use with encryption option
okay before as you will say with
encryption that's it now since we are
altering here if we try to create again
we'll get an error because that function
already exists there so you will have to
alter the function obviously to alter
the function we use the alter statement
and plus a five now the function should
have been encrypted now if I try to view
the text of the function either using SP
underscore help text system stored
procedure or graphically I get a message
stating that the text for the object is
encrypted okay if I try to view it even
by using the designer we get the same
message stating that you know this is
the text is encrypted you can see that
here so if you want to prevent the end
users from looking at the definition of
your function there is a way you know
you can actually encrypt that using with
encryption option for your function and
store procedures as well now can we
decrypt it absolutely there are ways to
do it but that's beyond the scope of
this video series we'll talk about that
maybe in a later session all right so
that's about encrypting function so we
have learned how to encrypt stored
procedures text using the encryption
option and part 18 of this video series
along the same lines you can also
encrypt a function text once encrypted
you cannot view the text of the function
using SP underscore help deck system
stored procedure or graphically using
the designer if you try to you will get
a message stating that the text for this
object is encrypted there are ways to
decrypt it but we'll talk about them
maybe in a later
so we use with encryption option there
all right now what do we mean by schema
binding a function this is very
important and a best practice as well
whenever you're creating a function okay
so if you look at this function let's
remove this with encryption option let's
alter this function once again so that
we can get our you know when I say SP
help text I get it back now because I
removed that with encryption now if you
look at this particular function this
function is dependent on TBL employee
stable okay now obviously if you want to
execute this function you'll say select
DB o which is nothing but the schema
name dot the name of the function and
the parameter it expects okay so when I
execute that it takes you it's fine now
without any problem now let us say
somebody has deleted this table okay now
look at this this function is one object
table is another object now I can delete
this table TBL employees by using drop
statements so drop table TBL employees
so when we delete this table okay
command completed successfully the table
got deleted okay now obviously when I
try to execute this function look at
this when we try to execute this
function let's see what's going to
happen it's going to fail because when
you execute this function what happens
this function tries to select the name
from TBL employees for employee ID one
but the table itself is not there so we
get an error message saying I cannot
find object TBL employees invalid object
TBL employees let's see that so when we
execute that we get that message invalid
object name TBL employees and that makes
sense right now
so don't you think this is bad you know
you have a function which is referring
this table TBL employees but you are
allowing the table to be deleted while
that function is still referring to that
table okay so your function breaks now
now can I prevent you know these kind of
changes to the underlying objects now
this function is dependent on TBL
employees so I don't want anybody to
change the table TBL employees in
anyway that would affect this function
is it possible absolutely how do we do
that that's what you know with schema
binding option is used for okay now
let's recreate this table I have this
clip for that already just to create the
table and populate the rows within that
so let's execute the script all right so
I have the table back now so the table
should be there so obviously now if I
execute the function it executes
successfully now let's alter the
function to basically include
with a schema binding option so I'm
saying with schema binding now let's
execute this okay so this is very
important look at the message that we
get it's saying we cannot schema bind
function f and get name by ID because
name TBL employees is invalid okay for
schema binding name must be in a two
part format and an object and an object
cannot reference itself okay so the name
should be in two parts that is you
should specify the two part naming
convention that is the schema name and
the actual table name
so here the schema is DB or standing for
database owner so once you specify that
then you will be able to schema bind so
basically what you are saying is this
function is bound to this table within
DB or schema the database owner schema
okay so let's now execute this command
completed successfully so we are schema
bound now so now if I try to drop the
stable employees let's see what happens
look at that we get the error message
stating cannot drop table TBL employees
because it is being still referenced by
object F and get name by ID okay so we
are safe now if somebody wants to
accidentally or inadvertently delete or
change the table in a way that affects
you know this function it won't be
allowed not only deleting if somebody
tries to change you know
this name column or ID column your
function will still be affected so these
kind of changes are deletions will not
be allowed as long as your function is
schema bound okay so that's the
advantage of using schema binding option
so schema binding option specifies that
the function is bound to the database
object that it references when schema
binding is specified the base object
cannot be modified in any way that would
affect the function definition the
function definition itself must be first
modified or dropped to remove
dependencies on the object that is to be
modified and obviously to use kimo
binding option you just specify with
schema binding in the definition of your
function and if you want to encrypt you
just specify with encryption it's so
simple 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 day