Please follow below advantages of SQL CLR than regular TSQL –
- SQL CLR computation power is extensive with wide range of options from foreach loops, strongly typed collections to LINQ API. Along with that the invocation costs of SQL CLR is very much less, because of CLR code is JIT compiled from the stub (code with in/out parameters reduces cost of object creations by ensuring SQL Semantics) to a function pointer which will be used at runtime for processing, which makes it prominent for heavy lifting computations.
- CLR UDFs are basically faster than TSQL UDFs, when it comes to computing results. But CLR functions are bit slower to TSQL functions when it comes to data access.
- If System defined aggregates have to be used then go for TSQL, or else for custom aggregates, go for CLR.
- Array processing is much faster in CLR than a cursor in TSQL.
- SQL CLR is good at managing memory on strings than TSQL because of CLR’s streaming SqlChars type. SqlChars not going to load the complete string into memory unlike SqlString. So SqlChars boosts up performance.
- Streaming TVFs return IEnumerable interface to the caller, typically TSQL. And IEnumerable is rich in its navigating properties which can be called “ON LOAD” basis and there is no need for the query plan to wait until all rows are populated instead it can get row by row and start processing. This way it is faster and efficient.