SQLCLR vs. T-SQL - Performance Comparison

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.

Read more: C# Corner

Posted via email from .NET Info