[MUSIC]
>> Hello everyone. My name is Colin Murphy.
I'm Senior Product Marketing Manager here at Microsoft.
I am joined today with Anna Thomas,
Data and Applied Scientist under Data Division in Microsoft.
Today we're going to talk about CLR
in SQL Server Azure SQL Database,
which is the Common Language Runtime.
So Anna, just can you explain a little bit
what is used for in SQL Server?
>> Yeah, sure. So it's basically used to author stored procedures,
custom triggers, it can even do things like
user-defined types, functions, and aggregates.
Because the managed code compiles natively,
it can actually result in
significant performance increases in some scenarios.
>> Cool. Can you show us
an example of a couple of stored procedures?
>> Yeah. So today I want to show you how to get started
with CLR in Azure SQL Database Managed Instance,
and how it's the same as SQL Server.
So the first thing we're going to do is actually
enable this in our instance.
By default it's not enabled.
So in order to do that,
we'll have to show the Advanced Options
and then simply enable CLR.
Once we've enabled CLR,
we can then do something like initialize
a schema called regex in this scenario.
Once we have a schema initialized,
we can then create an assembly.
An assembly is basically a reference
to that compiled program that we have.
So this is that compiled binary.
Now we can't upload the file,
but we can use the binary instead.
So that's what we're doing here.
Once we have that, we can then
start to match some of the functionality
that we had in our.NET program into our program here in SQL.
So what you can see that I'm doing,
is I'm creating three functions using SP Execute SQL,
and defining the functions regex match.
I'm just specifying the types there.
Then I'm matching them to the external name
of the assembly SQL CLR rejects,
and then the exact function within that,
that I want to be able to use in SQL.
Similar way, I'm doing it for substring to create a substring.
You'll see what these look like in a few minutes,
and as well as replace.
So we'll do it for replace.
Once we have these functions created,
we can then test them out.
This is where the cool bit comes into play.
So what you'll see here is I have a few sample texts.
If you're from the US or you're
familiar with Social Security Numbers,
you might notice that these are in a similar format as those.
With PII and GDPR,
we know this is very important to many folks.
But what I've done here is put
the regular expression pattern for Social Security Number.
If you're not familiar with those,
it's basically I'm saying, okay,
there we're going to have a number,
and there has to be three of them.
Then we might have a dash.
Then we'll have two more numbers,
maybe a dash, and then four more numbers.
That would be a Social Security Number.
>> Actually.
>> So then we're just going to see,
I can actually run this and we can see what each
of these functions does.
So what you can see is it's just put
each option into an unnamed column.
But here the first one is taking in this here,
it is what presumably might be a Social Security Number,
is saying, "Is it a match?"
We're getting a true because it's a match.
The second one is not a match,
so we're getting a zero there.
Now so the other function we did as a substring,
so where we could actually find Social Security Numbers within.
Maybe we want to put them on and further
process them or do something with them.
Then the last one is replace.
So we can actually replace or
mask those Social Security Numbers with x's.
So this is just one example of something cool that might be
interesting and easier to do in a language like.NET then in T-SQL.
>> Okay. But then you were just executing T-SQL here, correct?
>> Just executing T-SQL here,
we have native functions available for us to use,
so it really makes it so that anyone,
other users can also leverage these functions that we've created.
>> That's very cool. So if I'm developing
an application and I'm connecting to
my database and executing T-SQL.
I can use about custom functions
and just expand on my application.
>> Yeah.
>> That was really great. So I imagine there's
a lot of people who used this in
the past on-prem using SQL Server,
or other versions. You mentioned earlier on that
this is in Managed Instance,
so it is not available in Azure SQL Database,
the single version or elastic pools, correct?
>> Correct.
>> So it's just in Managed Instance.
So we've got some backwards compatibility.
If we want to move a application that's
typically on-prem to the Cloud,
we can use this version of Azure SQL Database Managed Instance.
>> Yeah, definitely. That's a great point
if you're migrating to the Cloud and want
to take advantage of the work
you already put into creating the CLR applications,
you can still leverage this in Azure.
>> Cool. Anna, thank you very much for showing me how CLR works.
Have you liked this little snippet,
and just give us a like if you find this information useful,
and we'll share a URL with you,
where you can actually learn more information
about CLR. Thank you.
>> Thanks.
[MUSIC]