This article discusses the feature of integrating the CLR (Common Language Runtime) into the SQL server. This is first write-up of multi-part series on this subject and is mostly an excerpt from referenced article/s.
Introduction and background
It’s quite a while after the SQL Server integration with CLR aka SQL CLR has hit the scene with SQL 2005. As a knee jerk reaction, language and DB developers/ administrators had a lot of hue and cry and the myths, misconceptions about it made more rounds in developers circle than the real SQL CLR features and usage.
Especially DB guys had lot of apprehensions about the security and performance. Also there was fear of it ringing the death bells for TSQL(Transact- SQL).
Fortunately, in reality SQL CLR is not like what it was taken for. It is not to replace TSQL rather it can better be made work in conjunction with TSQL. The overall developer community has still to embrace SQL CLR it its fullest abilities.
The security and performance are also the topics which have come up because of lack of understanding of SQL CLR. We will discuss these points as we go ahead.
Objective
To have a refreshed look at the SQL server integration with CLR by exploring What is SQLCLR, Why SQLCLR, and through few good examples, we would try to understand the power of SQL CLR
What is SQL CLR?
CLR i.e. Common Language Runtime is no more a black box for .net community and to reiterate, it is the heart of .NET framework which provides execution environment for code, provides services for type safety, exception handling, thread management, compilation (JIT), and memory management. The code which targets and runs in CLR is termed as managed code.
With SQL 2005 appearing at the developer’s desk, CLR hosting in SQL server was an additional feature introduced by Microsoft. This hosting enabled language users to author (and develop) the database items like stored procedures, user defined functions, triggers, UDT (user defined types) and aggregates in the managed code itself. This can be deployed for SQL server (where in the CLR feature is enabled). This deployment can be done through Visual Studio and also through TSQL, resulting in creating assembly in SQL server.
The overall steps to implement SQL CLR are as following
Why SQL CLR?
We are still to go through some demonstrations; still we can discuss why one should look forward to, while settling for SQL CLR.
1. Benefits of managed code
Compilation and Performance: Everybody knows that the managed code gets compiled to native code before execution (read about JIT for further details) giving the advantage on performance. TSQL is not managed code and hence doesn’t have this facility. Managed code can outscore T SQL for complex tasks like string operations, regular expressions and it can take advantage of BCL for file access, math operations, cryptography etc.
Security: The managed code has implements security essentials through Code Access Security (CAS), code links and application domains which in turn help to prevent certain operations. To comply with this, SQL 2005 (and above) employs CAS to secure such managed code. This security and safety can result into better alternative for extended stored procedures.
Better Programming Model: No doubt that the .Net framework languages are richer than T SQL in terms of constructs (i.e. types like arrays, collections, classes, for each loops etc. for data computation and manipulation) and programmability.
2. Standardized development
As Visual studios for .net, embed such database development, developers have consistent tools for database development, debugging as they would have for other .net framework services and components.
3. Testability through development environment
The feature for testing such database objects in SQL CLR is added into Visual Studio eliminating the need for accessing the database management and environment.
T SQL or Managed Code
Choosing between T SQL and managed code is not very intriguing task as it would sound. As mentioned earlier we are aware of the benefits of managed code and simultaneously we know what T SQL offers. The optimal approach would be use managed code for complex or CPU sensitive operations and also when libraries in .NET framework needs to be used and T SQL for data access mechanism.
Another advantage of managed code is that it can sit on client computer (nowadays client computers are also powerful and their utilization can be improved) while T SQL can’t.
Extended Stored Procedures and Managed Code
Extended stored procedures are ways to extend the functionality of SQL server wherein a function can be loaded and executed dynamically within a dll facilitating the actions and communications outside the SQL server.
The obvious choice when extended stored procedures are compared with managed code, is the managed code because of type safety, thread management and synchronization and seamless integration.
Summary and Conclusion
This article focuses on the theoratical part of SQL server integration i.e. what is SQL CLR And why SQL CLR. The SQL CLR is discussed in concise manner to throw light on the benefits and the steps to create such project. As usual any new technology poses challenges to architects and developers as when to use such technology and why. To help on this front, the process of choosing between the T SQL, extended stored procedure and SQL CLR (as a managed code) is discussed briefly. So definitely T SQL is going to stay (who knows for how long though).
Hope this helps to refresh our know-how of SQL CLR and readies us to delve further.
In the next part, we would see SQL CLR in action.
HAPPY PROGRAMMING!!!
Reference
Please see this link.