If you're just beginning to work with databases or you haven't kept up with all the new SQL Server 2005 features, then the relationship between the CLR and SQL Server 2005 Express is likely to be something of a mystery. Even if you're familiar with .NET programming, in which the CLR plays a major role, you still might be wondering how the CLR relates to a database.
Related: CLR or Not CLR: Is That the Question?
Most .NET programmers know that the CLR is essentially a runtime layer that sits between your .NET application and the OS. The CLR's job is to translate .NET program-generated instructions into native Win32 instructions that Windows OSs can execute. The primary benefits that the CLR provides are increased application security and stability. In addition, the CLR helps to protect the OS from problematic applications that might have memory leaks or buffer-overflow exploits. The idea behind the CLR is not new. Java, and its associated Java Virtual Machine (JVM) layer, was the first programming language to employ this type of architecture.
So how does the CLR work with SQL Server Express? The CLR is integrated with all SQL Server 2005 editions, including SQL Server Express. Creating database objects by using CLR objects provides some key advantages. First, it lets you create database objects--such as stored procedures, triggers, and functions--by using any .NET language such as Visual Basic (VB) and C#. Before the CLR was introduced, you had to use T-SQL commands to create database objects. Although T-SQL has excellent set-oriented data- retrieval capabilities, the .NET languages are better at handling complex logic and mathematical operations. Using the CLR opens up additional capabilities in SQL Server database object programming that weren't available before.
Second, T-SQL is designed to be a relational-database-oriented language and it doesn't include the ability to access resources outside of a relational database. In other words, T-SQL can't do things like read and write to the file system or registry. To give SQL Server the ability to access resources outside of a relational database, Microsoft added extended stored procedures, which are essentially native Win32 programs that run within the same in-memory process space as the SQL Server engine. Extended stored procedures fill the gap by providing access to external resources. But because these procedures run in the same process as the database engine, a bug in an extended stored procedure can bring down the SQL Server engine--not an ideal situation. However, integrating the CLR into SQL Server Express solves both of these problems. CLR objects can access external resources such as the file system and registry, but because CLR objects run within the confines of the CLR, they're isolated from the SQL Server engine, so a bug can't affect the functionality of the core database engine.
Integrating the CLR into the SQL Server 2005 product line adds functionality to SQL Server Express and lets you create database objects that contain complex logic and access external resources. To take advantage of the CLR, you have to program CLR objects by using one of the .NET languages and you need to enable CLR functionality by using the SQL Server Surface Area Configuration tool. (For security reasons, the CLR is turned off by default). I hope I've demystified the CLR and you now realize that it's just another SQL Server development tool.
