One of the SQL Server CLR full power---CLR introduction and Configuration

Microsoft SQL Server now has features that are integrated with the common language runtime (CLR) components of the Microsoft Windows. NET Framework. The CLR provides services for managed code, such as cross-language integration, code access security, object lifetime management, and debugging and analysis support. For SQL Server users and application developers, CLR integration means that you can now write storage using any. NET Framework language, including Microsoft Visual Basic. NET and Microsoft Visual C #. Procedures, triggers, user-defined types, user-defined functions (scalar functions and table-valued functions), and user-defined aggregate functions. Some of the key benefits of this integration are listed below:

• A better programming model.
• Improved security and security.
• Ability to define data types and aggregate functions.
• Streamlined development through a standardized environment.
• Has the potential to improve performance and scalability.

Since the CLR is so flexible, how do we make a choice between T-SQL and the CLR? In general, we should consider the use of clr:sql in a number of logical judgments and logical operations in the following cases, when we are mainly T-SQL. For example, you need to customize the encryption algorithm at the database level, decryption algorithm and so on. T-SQL cannot process requirements. For example, the need to make regular expressions in SQL judgment. Logical judgments are too complex to be handled using a large number of cursors. This is typically done because the CLR is not necessarily faster than the cursor, and the key is to see how you write.

Here's how to turn on the CLR and two ways to create a CLR. See the next section on how to write a CLR program in VS. Open the CLR. In SQL server2005/2008, the CLR is turned off by default. You can use the following SQL statement to open the CLR. sp_configure ' show advanced options ', 1; GO RECONFIGURE; GO sp_configure ' clr enabled ', 1; GO RECONFIGURE; GO

Create the CLR. Method One: Create ASSEMBLY AssemblyName from ' DllPath ' using DLL files for example: Create ASSEMBLY AssemblyName from ' C:/hello.dll '

Method Two: Create a ASSEMBLY assemblyname from file hexadecimal stream using a file 16 binary stream For example: Create ASSEMBLY AssemblyName from 0x123344 ... Where 0x12334 ... is the hexadecimal stream of the file C:/hello.dll. You can copy the hexadecimal stream of the associated DLL file using an editor such as UltraEdit.

By comparison, the method is simple and belongs to an external reference, which can be created to know which file it is. However, the DLL file path must be accessible by SQL Server, and the DLL will change with SQL Server when it is deployed. The second method is to write the stream into SQL Server and block the code to some extent. If this DLL is not very large, it is recommended to use the method and create the CLR.

One of the SQL Server CLR full power---CLR introduction and Configuration