In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server.
T-SQL is designed for direct access of data and for manipulation of that data. But T-SQL does not contain arrays, classes, collections, For-Each loop functionalities. However using SQL CLR, we can achieve all of this. With the integration of CLR in SQL Server, we can write managed code to define –
- Stored Procedures.
- User Defined Functions.
- Triggers.
- User Defined Type.
- User Defined Aggregate.
The decision to use CLR functionality under SQL server needs to be implemented when you are performing –
- CPU intensive operations.
- Procedures that perform complex logic.
- When you want to use BCL (Base class libraries) of .NET framework.
By default the CLR is not enabled under SQL Server. To enable CLR under SQL Server, execute the following command –
SP_CONFIGURE 'clr enabled',1
RECONFIGURE
Read more: SQL Server curry