Write stored procedures in SQL Server 2005 _mssql2005

However, in SQL Server 2005, we can use the. NET family language-primarily vb.net and C # to write stored procedures (and methods, triggers, and other components). Let's familiarize ourselves with 5 common questions about writing a new method for stored procedures. They are very worthy of our discussion.
1. Why do we have to use CLR mode to write stored procedures?
The main reason is speed. The SQL CLR runs faster in many ways: string processing, which runs much faster than T-SQL and is more powerful for error handling. At the same time, because the frameworks provided by the CLR to perform these transactions are more complete, any stored procedures that require transactional interaction with resources outside of the database-for example, file systems or Web Services--CLR SPS-are the best performing.
2. What types of stored procedures are the CLR best suited to write?
In general, it is best for the CLR to perform heavy calculations on the data rather than just the SP that queries the data. If a CLR SP encapsulates only a complex SELECT statement, then we will not see significant performance gains, because the SQL statements in the CLR must be validated each time the SP is run. In fact, it is worse than performing only the SELECT statement as a T-SQL sp.
A classic good way to do this is to encapsulate SQL on a regular SP if you need to execute a lot of SQL. If you want to run CLR-style processing on a large dataset, then we can call a regular SP inside the CLR SP to get this large dataset. In this way, the regular SP will be precompiled, performance will be better, and data conversion performance will improve.
Note: This scenario assumes that we need to do complex data processing on the database, rather than on the display layer. In fact, we need to consider these issues before we write code.
3. Should the existing stored procedure be converted to CLR mode?
Simply put, "do what is good." In this case, you can create an equivalent version of the CLR implementation for the specified stored procedure, and then test the two SPS using the actual data. Unless we can identify the new stored procedures: (a) Run as expected, (b) a real improvement in performance, or you should continue to use the old stored procedures. In fact, the CLR, like any other stored procedure, is nothing fancy.
4. Can you create a CLR (Common Language Runtime) stored procedure without developing the IDE?
Of course, we can implement this type of development manually through C # compilation. However, using Visual Studio or a similar IDE can be simpler, especially when we are converting or implementing a large number of SPS across the enterprise.
5. How difficult is the conversion?
Obviously, we must have knowledge of one of the supporting languages, such as vb.net or C #. In fact, SQL commands are "encapsulated" in CLR code, so as long as we know how to use it, it is not difficult to implement existing T-SQL in the CLR again. More difficult is how to use this language to optimize the work we are doing, the problem is not a few points can be summed up.