In earliers versions of SQL Server, developers, DBAs, and code-generator tools coded stored procedures, triggers, functions, and other objects in T-SQLand just T-SQL. However, if your skills are sharp enough, you can develop and call an Extended Stored Procedure (ESP) written in C or C++. Not many developers choose to take the ESP route, as it requires a thorough understanding of Open Data Services (ODS), COM, and other complex (and potentially destructive) technology. Not only that, but developers need to get their DBA to sign off on their use, and DBAs are naturally hesitant to do so. That's because if the ESP code screws up, it can (easily) bring down the server or worse[1], and you'll need someone to take the blame.
ESPs did not gain much traction back in the late 1990s, when they were first discussedthey were seen as too hard to code and maintain and far too dangerous. Understand that an ESP could kill SQL Server in a heartbeat. It's kinda like juggling with chainsawscool to watch until something goes wrong. As a result, ESPs were used very selectively to address some of the most CPU-intensive tasksthose well beyond the reach of T-SQL. For example, ESPs were implemented to perform complex geometry, trigonometry, or statistical calculations; expose low-level Windows API functionality; perform other complex CPU-intensive operations; or just provide functionality that T-SQL could not perform without assistance or a lot of code. Over the years, the SQL Server team exposed a number of system ESPs for use by the SQL Server tools and special DBA functions. One problem with using ESPs is that SQL Server must (by definition) fully trust the codethere is no "partial-trust" or "sorta-trust" option. "Trust" functionality is first enabled with SQL Server 2005. Because you use unmanaged code to create ESPs and they run in the same address space as SQL Server, a problem in an extended stored procedure could damage SQL Serverin fact, it could blue-screen the system. Not good. Now who was it that said we should use ESPs? Are CLR executables any safer than the ESP procedures they replace? No, not necessarily. If the CLR executable has to be marked as "Unsafe", it can do just as much damage as an ESP executable. Tip Microsoft wrote significant parts of SQL Server 2005 using CLR languages such as managed C++, C#, and Visual Basic .NET. Microsoft's ESP ReplacementIn SQL sServer 2005, T-SQL is still the language of choice for server-side executables, but you can also implement many of these executable objects in C# and Visual Basic .NET, and whatever .NET Framework CLR languages Microsoft chooses to add. In a nutshell, SQL Server 2005 exposes .NET Framework functionality by incorporating the ability to execute CLR code from within the SQL Server address space. This gives developers the option to:
The real question is: Should you use CLR executables? If you remember Jurassic Park (1993) and Jeff Goldbloom's character's speech to Mr. Hammond:
No, I don't think that CLR executables are reincarnations of carnivorous velociraptors or lumbering brontosauruses in disguise, but this technology is a radical departure from well-known and well-understood T-SQL and should be treated (like any new technology that I'm just learning) with a good measure of respect. I also hope you have doubly redundant high-tension fences placed strategically around your serverjust in case. |