Before comparing the performance of SQLCLR and TSQL, just have a quick look at the definition of SQLCLR.
SQLCLR is the Microsoft name for the integration of CLR as a hosted runtime within SQL SERVER. It enables the execution of managed code inside the database.
Simple SQLCLR Architecture
T-SQL is nothing but the Transact SQL which is the native language supported by SQL SERVER.
CLR supported in SQL Server provides an alternative to the procedural approach in T-SQL.
Now the question comes, when to choose SQLCLR and when to choose T-SQL for the procedural approach. We can't blindly decide, because each approach has strengths and weaknesses. So the decision should be made judiciously.
Thumb Rule
- If your logic mostly involves just the transformation of a large number of data, which can be easily performed by the declarative syntax of SQL, in that case T-SQL is preferred.
- If your logic mostly involves the complex calculation on a row basis with a small amount of data, SQLCLR is preferred.
From the above Thumb rule, another question may arise as below.
I have a large number of data and the complex calculations are done on the row basis, in that case which I should prefer?
It again depends on what you are going to do with the large number of data. Here we can't follow the thumb role. You need to test it with both the methods of large and small amount of data. A few inbuilt functions in SQL Server performs faster than in CLR functions and sometimes vice versa.
A comparison on String Split functionality
We need to split out strings in a string that has a delimiter and should be returned as a set of rows. This functionality is testing with above two approaches and the result is as follows.
Result - SQLCLR - Performs 3 times faster than T-SQL
So in this scenario SQLCLR performs better with a minimum amount of code.
- User declarative TSQL like select, insert, update, delete whenever possible.
- Procedural and row based approach is recommended only if it's not possible through normal TSQL command.
- If the logic involves row by row processing with complex operation in forward only direction, CLR is the efficient way.
- In some cases TSQL and SQLCLR should be combined to achieve a better performance.
So choosing the right one for the right scenario performs the best.