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.