Learn how to write and deploy CLR functionality written in .NET managed code into your SQL Server environment. You'll also get tips on when NOT to use CLR, as well as how to broach the topic with your database administrator.
Since SQL Server 2005, database stored procedures and functions can be written in .NET managed code targeted for the Common Language Runtime (CLR). This course walks the viewer through the steps required to create functions, stored procedures, triggers, aggregates, and user defined types with Visual Studio 2012. There is a specific module on how to approach a database administrator that may be less than eager to host CLR assemblies in SQL Server. There are also modules covering setup, permissions, troubleshooting, and times when it is not appropriate to use CLR in SQL Server.
Kevin is a lifelong learner with over 30 years in the IT industry. He works
on web and mobile applications as well as the databases and web services to
support them. With a gift for learning new languages, he is able to rapidly
apply his broad experience in new environments.
What Is a SQL CLR Extension? The first thing we need to do is talk about what a CLR extension is.
Why Not to Use CLR Extensions Although this course is about using CLR extensions, I think it's worth taking just a little time to talk about when not to use them. This segment is really short and could easily be contained within the previous segment describing what a CLR extension is. I chose to keep the topic of why you wouldn't use a CLR extension in a separate segment to make sure it stood out in the course outline. Now that I have your attention, please allow me to briefly discuss some reasons why you wouldn't want to use. NET managed code within your database projects.
Quick Start In this segment, we'll get right into building our first CLR extension. We're just going to hit the highlights for those that want a quick overview of what is involved. Later in the course, we'll make a project that is explained in more detail.
Discussion Topics for Your DBA You might get some push back from your database administrator about putting CLR extensions into the database. In this segment, I'll talk about how to approach the DBA and improve your chances of getting them to agree to using CLR extensions.
Functions In this segment, we will dive deeper into building CLR functions and deal with quite a bit of code.
Stored Procedures and Triggers In this segment, we'll look at what it takes to build Stored Procedures and Triggers using. NET-managed code. You can use the projects in this segment as starting points for making your own CLR stored procedures and triggers within your environment. Visual Studio does a pretty good job of creating the template for us when we add a CLR procedure or trigger. The projects in this segment can act as a reminder of what steps you need to accomplish when you create your projects in Visual Studio. We'll create a stored procedure project and test it with SQL Server Management Studio, then we'll do the same thing with a trigger project.
Aggregates We'll look at building an aggregate operator with CLR code in this segment. As I've mentioned earlier, there are a number of aggregates included in SQL Server. For the most part, any changes you may think you need can be accomplished with a tweak to a T-SQL query instead of requiring. NET managed code. When teaching about aggregates, many people use the behavior of aggregates when dealing with null values as a potential reason for writing an alternate solution with a CLR extension. This is actually just an excuse to demonstrate how to write an aggregate. I mention this because I'm going to follow suit. I've personally never encountered a situation where I absolutely needed to use. NET code to address a limitation of a SQL Server aggregate. I can usually work around situations by altering my query or casting values to specific datatypes. Since you may have a valid pressing need to write an aggregate in. NET managed code, I'll show you how to do it. There is currently no way to create a user-defined aggregate with T-SQL code. If you do have a need for one, you have to use a CLR extension to do it. Let's fabricate a scenario that calls for different behavior in an existing aggregate. We'll pick on the average operator.
User Defined Types In this segment, we'll take a look at writing a user-defined type in CLR. We cover a lot of code, so get ready. Before talking about user-defined types, I'm going to introduce a fabricated use case as an excuse to write a user-defined data type. Then we'll code the project. The code is somewhat lengthy, but I break it into small chunks. The clip regarding constants can be skipped if you only want information about user-defined types. I invite you to come back and watch it later though. After the project, we'll cover the attributes that are associated with user-defined types, and I'll wrap up with a discussion about deployment strategies.
Troubleshooting There are many things that can go wrong when dealing with CLR extensions. I can't predict everything you might encounter, but I'll cover some things I know can be troublesome. I've grouped the troubleshooting topics into related areas where possible. I start by reviewing problems with permissions. This is the most common problem when deploying CLR extensions. I then move on to general problems associated with using your CLR assemblies. Even if you're looking for a specific solution, it's worth going through each topic as a reminder or as a warning for what is required to resolve specific problems.