Do you really know, why there’s a need of Common Language Runtime (CLR) required instead of having T-SQL? Well I have some keynotes here:
- Transact-SQL is specifically designed for direct data access and manipulation in the database.
- As Transact-SQL excels at data access and management, it does not have programming constructs that make data manipulation and computation easy. Like they does not support arrays, collections, for-each loops, bit shifting, or classes.
- T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult.
- So in comes the Microsoft SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR).
- The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code.
- This enables developers to write procedures, triggers, and functions in any of the CLR languages, particularly Microsoft Visual C# .NET, Microsoft Visual Basic .NET, and Microsoft Visual C++.
- The code that runs within the CLR is referred to as managed code.
- Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions.
- One of the benefits of managed code is type safety, or the assurance that code accesses types only in well-defined, permissible ways. Before managed code is executed, the CLR verifies that the code is safe.
- With the functionality found in the .NET Framework Library, you also have the access to thousands of pre-built classes and routines.
- By default, the common language runtime is disabled in SQL Server and must be specifically enabled using a T-SQL SET command.
Hope you like this post.