Experts talk about SQL Server the CLR

Of the many highly recommended features of SQL Server 2005, one of the most useful programmers that can work with SQL Server is common Language Runtime, or the CLR. The CLR allows programmers to create stored procedures, triggers, user-defined functions, aggregates, and types directly in SQL Server. The CLR has a lot of promises, but there are some flaws.

There are some major reasons for the importance of the CLR. First, as SQL Server programming technology matures, code writers fall into some of the limitations of SQL Server itself and rely heavily on external code to perform some heavy porting. T-SQL (transaction processing SQL) is good at returning data sets, but it does not perform well except for this. The CLR makes the resolution of the problem possible and carries out data operations within SQL Server, which would otherwise require a completely separate program to implement. NET operation code and execution speed is much better than SQL Server/t-sql;. NET can run many times faster, when it is binary, rather than as a stored procedure to build.

Another great benefit of using the CLR is security. All code detects the type and security permissions before it is run. For example, memory that was not previously written is not allowed to be read by code in the problem. The CLR is also perfect;. NET Framework can be accessed from stored procedures, triggers, or user functions--in addition to dealing with classes that resemble user interfaces, it is not useful in SQL Server anyway.

To prevent CLR code from running Wild, Microsoft created a three-layer security model for CLR code calls: Safe, external_access and UNSAFE. Safe permission sets are essentially the same things that traditional stored procedures can do. You cannot make any modifications to it outside of SQL Server. External_access allows access to the registry and file system through. Net. Unsafe as its name. Code marked as unsafe cannot do anything, and it should not actually be used outside of a debugging or experimental environment. Most programmers should never need to use anything higher than the external_access level. (If you need to talk to a file system or registry in the context of a stored procedure or function, this may mean that you need to reconsider the logic of your attempt.) )

However, the CLR is not right for everything. On the one hand, it may be suitable for environments that are not easy to program and implement in T-SQL. Many simple operations can be done in the form of a stored procedure in T-SQL and do not need to be extended to an external process. This means context switching and additional transaction overhead, and any of these two items can first erase the speed elevation you get with the CLR. The CLR is best used to override extended stored procedures-for example, those that must be closed in the database but are cumbersome to be completed in T-SQL and not easily moved to the end of the business logic.

Another possible disadvantage is that, as SQL's leader, Rod Paddock, points out in his blog, if you are talking about an element in the business logic moving to the database, it can cause measurable problems. After all, SQL Server is better suited to a single large machine that is scaled up rather than across a few smaller machines (usually based on the business scale). This points out how important it is to have a selective use of the CLR. T-SQL is concise and effective; clr/. NET expensive and wide range. The right thing to do is to choose the right tool, although it's good to have a lot of choices.