ApsaraDB RDS:Use CLR integration to extend the capabilities of an ApsaraDB RDS for SQL Server instance

Background information

SQL Server Common Language Runtime (CLR) integration allows developers to write and execute stored procedures, triggers, user-defined functions (UDFs), and other database objects using .NET Framework programming languages such as C#. Through CLR integration, SQL Server can leverage .NET capabilities to handle more complex tasks, such as string operations, file processing, and regular expression parsing. It provides developers with more powerful tools, especially when dealing with compute-intensive tasks or business logic that T-SQL cannot effectively solve, making CLR integration a flexible and efficient solution.

Limits

This tutorial does not support shared or Serverless RDS SQL Server instances.

Preparations

public class SentimentAnalysis
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlDouble AnalyzeSentiment(SqlString text)
    {
        if (text.IsNull)
            return SqlDouble.Null;

        var sentimentDictionary = InitializeSentimentDictionary();

        string input = text.Value.ToLower();
        double sentimentScore = 0;
        int wordCount = 0;
        int i = 0;

        while (i < input.Length)
        {
            bool matched = false;
            // Match the longest word in the dictionary starting from the current position.
            foreach (var entry in sentimentDictionary.Keys.OrderByDescending(k => k.Length))
            {
                if (i + entry.Length <= input.Length && input.Substring(i, entry.Length) == entry)
                {
                    sentimentScore += sentimentDictionary[entry];
                    wordCount++;
                    i += entry.Length;  // Skip the matched words.
                    matched = true;
                    break;
                }
            }

            // If no word in the dictionary is matched, the character is skipped.
            if (!matched)
            {
                i++;
            }
        }

        return new SqlDouble(wordCount > 0 ? sentimentScore / wordCount : 0);
    }

    private static Dictionary<string, double> InitializeSentimentDictionary()
    {
        return new Dictionary<string, double>
        {
            {"good", 1.0}, {"like", 1.0}, {"excellent", 1.0}, {"great", 1.0}, {"satisfied", 0.8},
            {"nice", 0.6}, {"okay", 0.2}, {"average", 0},
            {"poor", -0.6}, {"terrible", -0.8}, {"disappointed", -0.8}, {"awful", -1.0}, {"hate", -1.0}
        };
    }
}

1. Write and compile the CLR program

Create a new class library project in Visual Studio 2022, copy the code from the Preparations section of this tutorial into the project, set the target framework to .NET Framework 4.7.2 or a later version, and then compile the project to generate a DLL file (such as CLRFuncDemo.dll). Make sure that the path of the generated DLL file (such as E:\Backup\CLRFuncDemo.dll) exists and is complete.

2. Create a CLR assembly and export it as a binary string

CREATE ASSEMBLY [CLRFuncDemo]
FROM 'E:\Backup\CLRFuncDemo.dll'
WITH PERMISSION_SET = SAFE

Use SQL Server Management Studio (SSMS) to export the assembly as a binary string. Copy the returned binary string for later use on your RDS SQL Server instance.

3. Import the CLR assembly to your RDS SQL Server instance

A user with DBO permissions on the current specified database can connect to the RDS SQL Server instance by using SSMS and create a CLR assembly by using the binary string. Execute the following statement:

-- Create an assembly
CREATE ASSEMBLY [CLRFuncDemo]
FROM 0x4D5A9000... -- Replace with the actual binary string
WITH PERMISSION_SET = SAFE;

-- Create a CLR function
CREATE FUNCTION dbo.AnalyzeSentiment(@text NVARCHAR(MAX))
RETURNS FLOAT
AS EXTERNAL NAME CLRFuncDemo.[SentimentAnalysis].AnalyzeSentiment;

As shown in the following figure, the assembly and CLR function have been successfully imported and created.

4. Test the CLR function

Test the CLR function by using a simple table. Test data:

CREATE TABLE UserReviews (
    ReviewID INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50),
    ProductName NVARCHAR(100),
    ReviewContent NVARCHAR(MAX),
    ReviewDate DATETIME
)


INSERT INTO UserReviews (Username,ProductName,ReviewContent,ReviewDate) 
VALUES 
    ('Chen Qi','Smartphone E',N'This phone is terrible, battery life is awful, I am very disappointed!','2024-03-25 13:00:00'),
    ('Zhou Ba','Laptop F',N'Very poor experience, bad cooling, terrible performance, not recommended!','2024-03-26 14:30:00'),
    ('Sun Jiu','Headphones G',N'Sound quality is terrible, these headphones are awful, very disappointing!','2024-03-27 16:10:00'),
    ('Wu Shi','Tablet H',N'This tablet is okay, average performance, usable.','2024-03-28 10:00:00'),
    ('Zhang Yi','Smartphone I',N'The phone is average, nothing particularly good or bad, just mediocre.','2024-03-29 11:20:00'),
	('Li Si','Laptop B',N'This laptop is excellent! Great performance, I love it, extremely satisfied!','2024-03-20 09:15:00'),
    ('Wang Wu','Headphones C',N'Sound is great, good build quality, excellent user experience, I am very satisfied!','2024-03-22 11:00:00'),
    ('Zhao Liu','Tablet D',N'This tablet is really good, powerful performance, good battery life, I really like it!','2024-03-24 15:45:00'),
	('Wang Kang','Smartphone K',N'The phone is very user-friendly, excellent camera performance, I really like it!','2024-03-31 12:05:00');



SELECT 
    ReviewID,
    Username,
    ProductName,
    ReviewContent,
    dbo.AnalyzeSentiment(ReviewContent) AS SentimentScore,
    CASE 
        WHEN dbo.AnalyzeSentiment(ReviewContent) > 0.3 THEN 'Positive'
        WHEN dbo.AnalyzeSentiment(ReviewContent) < -0.3 THEN 'Negative'
        ELSE 'Neutral'
    END AS SentimentCategory
FROM UserReviews
ORDER BY SentimentScore DESC

As shown in the following figure, the results basically meet expectations. The function extracts keywords from comments to determine whether the average value indicates a positive or negative review.

Possible security issues

If you encounter the following error when you create or modify the CLR assembly CLRFuncDemo on your RDS SQL Server instance, you can use one of the following methods to resolve the error:

Msg 10343, Level 14, State 1, Line 12
CREATE or ALTER ASSEMBLY for assembly 'CLRFuncDemo' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

This error occurs because starting from SQL Server 2017, Microsoft has imposed stricter security restrictions on CLR integration by introducing the clr strict security option with a default value of 1. This ensures that only signed assemblies can be loaded with security levels (SAFE or EXTERNAL_ACCESS).

You can use one of the following methods to resolve the error:

Summary

CLR integration provides powerful extension capabilities for SQL Server, allowing developers to leverage the rich features of .NET Framework to handle complex database tasks. This topic describes how to deploy and use CLR integration on your RDS instance and provides examples.

CLR integration enhances the use scenarios of SQL Server. CLR integration allows you to process tasks that cannot be managed by T-SQL.

• Complex string processing: You can use the .NET regular expression library to implement advanced text processing, such as medical record text parsing and data cleansing.

• High-performance computing: CLR integration delivers high compilation performance. For example, you can process complex mathematical operations, such as option pricing in the financial field.

• File and network operations: CLR integration supports cross-system data exchange. For example, you can enable automated document processing in the logistics industry.

• Custom encryption and decryption: CLR integration ensures the security of sensitive data. For example, CLR integration provides end-to-end encryption solutions for the banking industry.

• Image processing: CLR integration provides multimedia data processing capabilities. For example, you can analyze product images and test product quality in the retail industry.

• Machine learning (ML) integration: CLR integration provides intelligent analysis features, such as real-time product recommendation on e-commerce platforms.

• Complex business logic: CLR integration is suitable for multi-dimensional decision-making scenarios, such as credit card limit evaluation.

If you use CLR integration in databases rather than in applications, the following benefits are provided:

• Performance: Direct database operations can reduce data transmission overhead and provide high processing efficiency.

• Security: The sensitive data is processed within databases, which can effectively reduce risks.

• Maintenance: The centralized storage of business logic facilitates centralized management and version control. In addition, multiple application systems can share business rules, which improves efficiency and consistency.

In most cases, T-SQL and the application layer are used to implement features. T-SQL is suitable for data queries, processing, and underlying business logic. The application layer is suitable for complex interactions and workflows. CLR is a supplementary option for T-SQL and the application layer. If your workloads involve operations such as complex string processing, high-performance computing, or external system integration, you can use CLR as a complementary solution. This applies if T-SQL fails to be implemented and limits are imposed on the performance, deployment, and security of the application layer.