12,064,498 members (57,383 online)
We usually face a problem in Stored Procedures and other database objects when we need to implement some complicated logic within it. We found inefficient performance when we tried to implement complex logic & business rules in database objects. In many cases, we found C# or VB classes more powerful to implement such things. Microsoft has added a new feature to address such issues with SQL server 2005 called "CLR Stored Procedure".
Now, let us understand CLR stored procedure. CLR, as most .NET programmers know, is Common Language Runtime and Stored Procedures are routine stored procedures of database. Thus, CLR Stored Procedures are a combination of both. As we all know, Common Language Runtime is a core .NET component. The Common Language Runtime is runtime execution environment which supplies managed code with various services like cross language integration, code access security, lifetime management of object, resources management, threading, debugging & type safety, etc. So now, CLR Stored Procedures are .NET objects which run in the memory of database.
The very first usage of CLR Stored Procedures can be said is accessing system resources. Accessing system resources could also be done using Extended Stored Procedures which are again database objects like Stored Procedures, Functions, etc. Extended Stored Procedures can do most of the things which a standard executable program can do. Then, why have CLR Stored Procedures? The very first advantage of CLR Stored Procedures is that it is a managed object unlike Extended Stored Procedures, which are unmanaged objects. The common thing between them is that both run under database memory. In this way, CLR Stored Procedures give all the benefits of managed objects. The following screen explains memory allocation while executing a CLR Stored Procedure.
Extended stored procedures run in the same process space as the database engine, memory leaks, bugs, etc. can affect the performance of database engine. CLR stored procedures resolve these issues as they are managed objects and run as per specifications of Common Language Runtime. CLR Stored Procedures can replace a standard stored procedure that contains complex logic and business rules. CLR Stored Procedures take benefit of .NET classes and thus make it easy to implement complex logic, calculation, intense string operations, complex iterations, data encryption, etc. that are difficult to obtain in standard stored procedures. Standard stored procedures are still best for data oriented tasks. CLR Stored Procedures not only include stored procedures but also include Functions, Triggers, etc. CLR Stored Procedures are compiled one so they give better performance.
You are the best judge when to use regular Stored Procedures and when to use CLR Stored Procedures. CLR Stored Procedures can be used in the following scenarios:
Let us create one simple CLR Stored Procedure which fetches all the rows from one table of the database. I have listed all SQL statements used for creating database, creating table, inserting dummy records in the table, etc. under "SQL statements used in the demo" section.
context connection=true" as connection string in the constructor while creating new
SqlConnection. This CLR stored procedure is going to be the part of the database, so it will be the internal part of database and there is no need to connect database externally. So, no need to provide connection string that we usually provide in applications. Then Click on Build menu >> Click on Build Solution. Also click on Build menu >> Deploy solution. This will deploy the assembly to the database for which we have made connection initially.
sp_configure 'clr enabled', 1
Run the following query to take effect or the above query.
Now, execute the stored procedure. It should give similar results shown in the following screen:
Following are the queries to create database, create table, insert records in the table, etc. --Create a new database for demo
CREATE DATABASE DbForClrDemo --Use database USE DbFo
Following are the queries to create database, create table, insert records in the table, etc.
–Create a new database for demo CREATE –Use database USE DbForClrDemo –Create table for CustomerSalesInformation CREATE TABLE [dbo].[CustomerSalesInformation]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Sales] [decimal](18, 2) NOT NULL DEFAULT ((0)), CONSTRAINT [PK_CustomerSalesInformation] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] –Insert dummy data to CustomerSalesInformation table INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Virat Kothari’, 50000) INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Dhruval Shah’, 5000) INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Urvish Sheth’, 15000) INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Rakesh Bajania’, 25000) INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (‘Dhaval Shah’, 150000) –Enable CLR Stored Procedure in database sp_configure ‘clr enabled’, 1 –Run following statement to take effect of above statement RECONFIGURE –Now execute our CLR Stored Procedure. Remember “ClrDemo” -is name of our Stored Procedure EXEC [dbo].ClrDemo rClrDemo
CLR Stored procedures are very simple and can be used in most complex scenarios.