It’s just SQL: CLR in Azure SQL Database Managed Instance | Data Exposed


[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]

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *