Let's talk about "Rewriting User-Defined Functions".
In this lesson, I'm going to be talking to you about
what a user-defined function is,
giving you just a couple of different types
of user-defined functions,
and then talking briefly about how we can use T-SQL
to create a UDF in Synapse.
All right.
With that, let's talk about user-defined functions.
So what is a UDF?
Well, it's a main tool that programmers use
for creating applications with reusable code.
So,
using a UDF, it allows you to create your own routine
or procedure rather than using one of the built-in routines.
Why do you need a UDF?
Well, UDFs decrease execution time,
they reduce cost, and they allow you
to do modular programming.
All 3 of those things can be very beneficial
if you need something that's slightly out of the parameters
of a built-in function.
There are 3 basic types of UDFs.
There's a scaler function.
So this is a function that returns a single value,
and it can either be a single or a multi-statement function.
You can have a table-valued Function.
This table-valued function returns a... wait for it...
Table. Table data type.
That's what a table-valued function does.
And then we also have system functions.
So the system functions are just the built-in functions,
and they can do all kinds of various operations.
But those are the 3 different types
of functions that you can have.
All right.
So with that, we're actually going to switch now,
and we're going to talk just a little bit about the syntax
or the code of creating a UDF using T-SQL.
Now, looking at this code base, I want to tell you
just a couple of things.
One, we don't need to know very much
about this for the DP-203. So we're just going to go over
a couple of really basic points.
The first being this CREATE FUNCTION here.
So this is the start, and we're going to put information in
about our schema_name, parameters, and that's all going to
be very specific to what we want to do with our UDF.
Now, the next piece that you need to look
at is this BEGIN and END statement.
The BEGIN and END statement is important
because it doesn't have any side effects,
and by side effects, I mean any permanent changes
to the state of a resource
that has any scope outside of this function.
So basically, it's not going to make any permanent changes
to anything outside of this function.
The other thing that I want to point out to you is down here
at the bottom, this SCHEMABINDING.
SCHEMABINDING is a clause that binds the function
to the schema of any objects it references.
So tables, views, other UDFs.
So if you are attempting to change something
that's referenced within that schema, it's going to fail.
All right, I'm not going to go any further than that.
I am positive that quite a few of you are coders
and probably know way more about UDFs than I do,
but I can tell you for the DP-203, we don't need
to go any further than this, just the basics.
So with that, let's talk about just a couple of key points.
One, UDFs: that's T-SQL and Synapse.
So for the DP-203, that's what's important.
And then remember the basics of the T-SQL syntax.
The basics. You don't need to dive very far.
Short lesson. Key point: UDFs, T-SQL in Synapse.
All right, with that, let's jump on the next lesson.