CLR Integration Vs Extended Stored Procedures

Writing code in a higher-level language isn't a brand new concept. In fact, even in SQL Server 2000 you could write extended stored procedures in a language such as C++ and run them directly inside the database. So, what does CLR integration offer that extended stored procedures don't? Consider the following:

1. CLR objects inside SQL Server allow you to neatly categorize them into three buckets of access restrictions. The developers at Microsoft took a long, hard look at every available class in the .NET framework and categorized them into three categories. Depending upon the access restriction your CLR object is under, the CLR object is restricted from using certain features of the framework. The three categories are as follows:
• SAFE: This is the default level and it is the most restrictive. This means that your code does not need any external resources in addition to the operation. Safe code can access data from the local SQL Server databases or perform computations and business logic.
• EXTERNAL_ACCESS: This level signifies that certain external resources, such as files, networks, Web services, environmental variables, and the Registry, are accessible.
• UNSAFE: This level, which you should try very hard to avoid, specifies that your code is allowed to do anything. In other words, you are requesting to be free of any granular-level control, and thus have the same permissions as an extended stored procedure.
2. Extended stored procedures are written in C++. CLR objects can be written in .NET-compliant languages such as C# or VB.NET, which are safer and easier to use.
3. CLR code is much more reliable than the native unmanaged code that extended stored procedures are written in. This is because you are freed from issues such as memory management, and your code is tied to appropriate access restrictions based on the code access security model of the .NET Framework.
4. CLR code has the ability to work with newly introduced data types such as varchar(max), varbinary(max), and XML.
5. CLR code has the ability to latch on to the current running database connection, also referred to as the context connection. Extended procedures have no option but to create a loop back connection, which involves a significant overhead.
6. CLR objects can be of various types, stored procedures, triggers, UDFs, TVFs, and aggregates, whereas extended stored procedures can only be extended stored procedures.
7. Native unmanaged code runs faster than .NET 2.0 code in most cases. This means that unless your code explicitly needs to work with the local data store it is operating on, native unmanaged code or extended stored procedures will in general perform better than CLR objects. There may be other factors affecting the actual performance, such as native-to-managed code transitions. This, however, is a very weak reason to give up the compelling advantages that the CLR presents you in general. In fact, with the introduction of CLR integration, you should almost never have to write extended stored procedures.