Among the many highly touted features in SQL Server 2005, the one that probably has the most relevance for programmers who work with SQL Server is the Common Language Runtime, or CLR for short. CLR allows programmers to create stored procedures, triggers, user-defined functions, aggregates and types directly inside SQL Server. CLR has a lot of promise, but it also has some pitfalls.
There are several big reasons why CLR is important. First, as SQL Server programming has matured, coders are running into the limits of what's possible in SQL Server itself and relying heavily on external code to do a lot of the heavy lifting. T-SQL (Transact-SQL) is great at returning data sets, but it's not good for much else beyond that. CLR makes it possible to solve problems and produce data manipulations inside SQL Server that would normally require a completely separate program to pull off. .NET handles code and execution speeds a lot better than SQL Server/T-SQL; the same bit of code in .NET would run many times faster as a binary than if it were built as a stored procedure.
Another big advantage of using CLR: safety. All code is checked for type and permissions safety before it's run. For instance, memory that has not been written to previously is not read by the code in question. CLR is also quite complete; everything in .NET's framework can be accessed from within a stored procedure, trigger or user function -- except for classes that deal with things like the user interface, which wouldn't be useful in SQL Server anyway.
To keep CLR code from running amok, Microsoft created a three-tier security model for how CLR code can be invoked: SAFE, EXTERNAL_ACCESS and UNSAFE. The SAFE permissions set is essentially the same as what a conventional stored procedure would be able to do. It can't modify anything outside of SQL Server itself. EXTERNAL_ACCESS allows access through .NET to the Registry and the file system. UNSAFE is aptly named. Code marked as UNSAFE can't do anything, and it should really not be used outside of a debugging or experimental context. Most programmers should never need to use anything higher than EXTERNAL_ACCESS. (If you need to talk to the file system or the Registry from within the context of a stored procedure or function, it's probably a sign that you should rethink the logistics of what you're attempting to do.)
CLR is not suitable for everything, however. For one thing, it is probably best suited for situations that are not easy, programmatically, to accomplish in T-SQL. Many simple operations can be done as stored procedures in T-SQL and don't need to be farmed out to an external process. That implies context switching and additional transactional overhead, either of which alone might kill whatever speed benefits you get from using CLR in the first place. CLR is best used for replacing extended stored procedures -- i.e., things that must be close to the database but are too cumbersome to be handled elegantly in T-SQL and can't be moved easily into the business logic end of things.
Another possible downside: As SQL guru Rod Paddock pointed out in his blog, if you move certain elements of the business logic closer to the database, that may cause scalability problems. SQL Server, after all, is better suited to being scaled up on a single big machine rather than across several smaller ones (which is how business logic is typically scaled). This points out how important it is to be very selective about what CLR is used for. T-SQL is compact and efficient; CLR/.NET is expansive and inclusive. The right job will demand the right tool, although it is nice to have that many choices.
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
