SQL Server Overview of CLR Features

Of the many highly rated features of SQL Server 2005, one of the most suitable people for programming with SQL Server is the common language runtime, abbreviated to the CLR (Common Language Runtime). The CLR allows programmers to create stored procedures, triggers, user-defined functions, collections, and types directly in SQL Server. The CLR has a lot of promises, but it also has some drawbacks.

There are several larger reasons for the importance of the CLR. First, because SQL Server programming is ripe, the encoder runs within the limits of SQL Server itself and relies heavily on external code to perform some heavy operations. T-SQL (Transact-SQL) is good at returning data collections, but not in other ways. The CLR makes it possible to resolve problems and reduce data replication within SQL Server by requiring complete separation of programs in SQL Server. NET manipulates code and executes more quickly than SQL Server and T-SQL; code in the same position in. NET is so binary that it is still running many times faster than it was built as a stored procedure.

Another great benefit of using the CLR is security. All code detects type and license security before running. For example, memory that was not previously written is not accessed by code in the request. The CLR is also very sophisticated; what is known in the. NET Framework can be accessed in stored procedures, triggers, or user functions-except for classes that handle similar user interfaces, which are not useful in SQL Server.

To avoid the madness of the CLR, Microsoft has created a three-tier security model that sets out how CLR code is invoked: security, external access, and insecurity (safe, external_access and UNSAFE). Security permission settings are as important as traditional stored procedures that can be executed. It cannot be modified by anything other than SQL Server itself. External access is allowed through. NET to access the registry and file system. Unsafe naming is appropriate. Code that is marked as unsafe cannot do anything, and they are actually unusable outside of the debug or test environment. Most programmers do not need to use things that are higher than external access. (If you need to access the file system or registry within the context of a stored procedure or function, it is likely to mark the logic that you need to reconsider what you are doing.) )

However, the CLR does not fit all things. One thing, it might be best for environments that are not easy, need to be programmed, and implemented in T-SQL. Many simple operations can be done as stored procedures in T-SQL and do not require external processing. This means context substitution and additional transaction burdens, each of which offsets the most significant speed improvements you can bring with the CLR. The CLR is best at replacing extended stored procedures-for example, those closely related to the database, but T-SQL is too cumbersome to handle, and it is difficult to easily move to the business logic side of things.

Another possible disadvantage is that, as SQL's leader, Rod Paddock, points out in his blog, if you move a part of the business logic closer to the database, it can cause measurable problems. In any case, SQL Server is better suited for scaling up on a single mainframe rather than on multiple smaller machines (this is usually the way business logic is measured). The above points out how important it is to have a selective use of the CLR. T-SQL is very compact and efficient; Clr/.net is extensible and inclusive. The right thing to do is to use the right tools, though it's good to have a lot of choices.