Originally posted in Idera: http://blog.idera.com/sql-server/performance-and-monitoring/comparing-simple-efficiencies-t-sql-udf-vs-sqclr-udf-for-splitting-strings/
Recap of T-SQL vs SQLCLR (pseudo) Debate
There have been numerous posts about benefits of using T-SQL vs SQLCLR, and vice versa. And we all know the mantra – T-SQL for data access, SQLCLR for anything that is computationally intense tasks.
SQL Server is a relational database, and works best with set based operations and direct data access. If you need to do straightforward INSERTs, UPDATEs, DELETEs, SELECTs, stick with T-SQL unless you want to do lots of overtime trying to figure out why you’re data access suddenly became slow.
So when do we use SQLCLR instead of T-SQL?
There are already lots of discussions – even books – about SQLCLR advantages. I will defer you to them (check out the references section), but I will provide a very brief list of scenarios when you might want to consider SQLCLR:
- Interaction outside SQL Server If you need to work with the OS, files, registry etc.
- Validation If you need to validate phone numbers, email addresses, postal codes, or any patterns
- Complex computations If you need running aggregates, complex math equations (what is the square root of x to the nth power divided by 2 * pi?), financial analytics maybe?
- Custom Data Types If you need to create your own custom business-specific data types. We also have to remember that XML and GEOGRAPHY/GEOMETRY are great additions to SQL Server, and these are technically CLR data types.
The debate between T-SQL vs SQLCLR sometimes can be taken out of context. I call it a pseudodebate, because sometimes it’s made to seem that SQLCLR is meant to replace T-SQL, when it’s not. SQLCLR is *not* evil. It’s just another tool to help you do your job. It *can* become evil though, if you misuse it.
While there are some overlaps in scenarios where you can use both, these two should really be complementary. Where T-SQL is slow or lacking, SQLCLR should at least be considered and tested. Again, consider the right tool for the right job.
Read more
Like this:
Like Loading...
