The SET STATISTICS TIME ON command asks SQL Server for the elapsed and CPU times for each command.
The results are summed so that the time to return results to the client isn't a factor in the measurement.
Trans 'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System. Sec 'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System. Xml 'sqlservr.exe' (Managed): Loaded 'ns_txt', Symbols loaded.
Auto-attach to process ' sqlservr.exe' on machine 'nsl6' succeeded.
The CPU time for the SQLCLR is only 47 milliseconds compared to 6.187 seconds for the T-SQL UDF.
The SQLCLR function is 131 times faster, a dramatic difference.
Is Match function returns a CLR Boolean, which is returned by the function as a Sql Boolean.
(0 row(s) returned) Finished running sp_executesql. So the new function passes the original set of tests. Is the SQLCLR function really faster than the old T-SQL UDF?
I needed a faster alternative, what options do I have?
While I could have improved the T-SQL in my function validating e-mail addresses, which requires a lot of string handling, T-SQL isn't the best technology for this problem. Net code handles better then T-SQL a SQLCLR scalar function can be a great solution.
When you send e-mail to large lists, validating the e-mail addresses before sending out the e-mail is worth doing to prevent having mail rejection messages clog up your mail server.
I had been doing the validation with a T-SQL User Defined Function (UDF), but it was getting too slow as the number of e-mails grew.