Is SQLCLR good or evil? Thomas LaRock (Blog | @SQLRockstar) is asking that question this month in his #MemeMonday blog party.
Let’s start with a different question. Is jagermeister good or evil? If you’ve been to SQLKaraoke at the PASS Summit then you would definitely say that it is good. But is the answer the same the next morning, during the key note? As a consultant, the only answer I can offer is “it depends”. It depends on whether or not there was any jager still available at the bar at the end of the night.
SQLCLR is Jager
SQLCLR is much like jager. Given the right situation and proper moderation, it is exactly what you need. The uses are limited, but so are the uses for nearly every feature in SQL Server. It isn’t appropriate to use Log Shipping for a reporting database. And it isn’t appropriate to use columnstore indexes on OLTP tables. You can do all of these things, but you won’t see the benefit of the feature that you would in its proper application.
Where is the proper application of SQLCLR? There are two use cases that I most often point DBAs when considering CLR.
File System Interaction
The first is when you need to interact with the file system and you want to avoid using xp_cmdshell. With xp_cmdshell, you can access the operating system and build scripts that can manage files as you need. The downside to using xp_cmdshell is that there are no controls to limit what xp_cmdshell can be used for. SQLCLR fixes this problem.
Through SQLCLR, you can control what file system actions can be used. They can be constrained to specific users and the formats for them configured in ways that you can use to interact with other database objects through T-SQL. In the end, all of this can be done without giving away the keys to securing the SQL Server to everyone.
To try out this example of using SQLCLR, check out FileSystemHelper SQL Server CLR.
String Manipulations
The second is situation is where there are string modifications that need to be done where doing those would be overly cumbersome with native T-SQL. Imagine receiving a large XML document, like blocked processor deadlock report, how much effort would it take to reformat the XML document to an e-mail that is easy to digest.
The SQL statement would require multiple nested XQuery, string concatenation, and html tags to get the formatting proper. In the end, the statements are likely to be fragile and difficult to manage. Instead, consider using an XLST document to format the XML document into a properly formatted HTML document for the e-mail. Instead of managing a complicated concatenation calculation, you’ll have an XLST that be managed as a template versus code in the database.
To try out this example of using SQLCLR, check out StringHelper SQL Server CLR.
Summary
Hopefully, these two examples were useful. It is important to know that SQLCLR can’t be good or evil. It just is. How you use it or how the people in your environment is what determines the good or the bad.
Related posts:
