Recently, I’ve been doing a lot of invoking .NET Assemblies within SQL Server 2005. I blogged an overview of SQLCLR here. Also, I posted about how to implement aggregation function in C# here.

Invoking .NET code has many advantages, but at the same time, we should be aware of the performance, that’s why I tested my CountNulls Aggregation function – created in a previous post – with the SQL Server Profiler tool.

I tested the function on the Person.Contact table in the AdventureWorks database, which contains 19972 records.

SELECT dbo.CountNulls(MiddleName)  from Person.Contact

The duration time was ranging between 93 and 154 milliseconds

While the following T-SQL statement gives the same results but with duration time ranges from 1 to 3 milliseconds maximum. 

Select count(*) as from Person.Contact where MiddleName is null

As a result, we should not move to use SQLCLR unless there is a clear sign that means to implement a function in .NET we have to give T-SQL a chance, and only when T-SQL fails, we then use .NET Assemblies.