Introduction
Before comparing the performance of SQLCLR and TSQL, look at the definition of SQLCLR.
SQLCLR
SQLCLR is the Microsoft name for integrating CLR as a hosted runtime within SQL SERVER. It enables the execution of managed code inside the database.
Architecture of SQLCLR
![sql.gif]()
T-SQL is nothing but 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 complex calculations 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 a row basis; in that case, which I should prefer?
It again depends on what you will do with a 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 amounts of data. A few inbuilt functions in SQL Server perform faster than in CLR functions and sometimes vice versa.
Comparison of 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 tested with the above two approaches, and the result is as follows.
Result - SQLCLR - Performs three times faster than T-SQL.
So in this scenario, SQLCLR performs better with minimum code.
- User declarative TSQL like select, insert, update, and delete whenever possible.
- A procedural and row-based approach is recommended only if it's not possible through normal TSQL commands.
- If the logic involves row-by-row processing with complex operations in a forward-only direction, CLR is the efficient way.
- In some cases, TSQL and SQLCLR should be combined to achieve better performance.
So choosing the right one for the right scenario performs the best.