Using CLR Stored Procedures in SQL Server

You can create CLR stored procedures in SQL Server. This article by Jignesh Trivedi provides some basic information on how to get the job done.

In SQL Server 2005 and later versions we are able to create CLR database objects, in other words functions, Stored Procedures, triggers and so on. Sometimes these CLR objects are faster than T-SQL. Generally a CLR database object can be used when we want to implement logic that is not possible with T-SQL or it can use more resources.

Advantages of CLR Stored Procedure

  • CLR Stored Procedures are managed code so it ensures type safety, memory management and so on. 
  • It provides Object Oriented Programming capability hence it enables us to use encapsulation, polymorphism and inheritance.
  • A CLR Stored Procedure can be written in C#, VB.NET or any other language supported by the .NET Framework. 
  • It is very useful when executing complex logic like intense string operation or string manipulations, cryptography, accessing a third-party library, accessing system resources and file management and so on. 

Limitations of CLR Stored Procedure

  • Deployment may be difficult in some scenarios.
  • It is not convenient in all contexts, such as it should not be used to execute the simplest queries.
Advertisements

One thought on “Using CLR Stored Procedures in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s