One of the powerful features of SQL Server 2005 is the ability to write SQL Server objects (stored procedures, triggers, user-defined functions, user-defined aggregates, user-defined types) with managed code.
One of the question I see that comes so "natural" is: "I've to implement this object. What should I use? T-SQL or SQL CLR?".
To answer this question it's not so simple and it depends a lot on what you want to implement and the result that you want to obtain. However, a preface must be clear to all: SQL CLR is not a replacement for T-SQL, but it complements T-SQL!
The main differences of T-SQL vs SQL CLR regards:
- Code execution: T_SQL is interpreted, SQL CLR is compiled
- SQL Server access: T-SQL has a direct access to SQL, SQL CLR is In-Process
- T-SQL doesn't support complex data types and doesn't have access to the :NET Framework classes, while SQL CLR has these features.
So, when using T-SQL and when using SQL CLR?
If you're performing basic CRUD operations, T-SQL is the best choice. If you have a task that involves intensing cursor processing or access to external resources, SQL CLR is the best choice. Remember that all SQL CLR code is enlisted in the current transaction of the T-SQL code that called it, so if you've long running SQL CLR procedures your transaction could become too long.
These are in my opinion the "starting guides" in order to perform the best choice for a task.
Last thing: remember that you must enable CLR support on your SQL Server in order to use it. A common and quick way to enable CLR suport is to run this script via SQL Server Management Studio:
use master
go
sp_configure 'clr enabled',1
go
reconfigure
go
That's all... you're ready to start. 