SQLCLR vs. T-SQL - Performance Comparison

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

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.