How CLR functions compress NTEXT type fields

Editor to share with you how the CLR function compresses NTEXT type fields. I hope you will get something after reading this article. Let's discuss it together.

CLR (common language runtime) is a runtime environment like the Java virtual machine, which is responsible for resource management (memory allocation and garbage collection) and ensures the necessary separation between the application and the underlying operating system. In order to improve the reliability of the platform and to achieve the level of stability required by transaction-oriented e-commerce applications, CLR is responsible for other tasks, such as monitoring the running of programs. According to .NET, programs running under CLR supervision are "managed" code, while applications or components that are not under CLR and run directly on bare metal are "unmanaged" code. You can create database objects in SQL Server instances that can be programmed in assemblies created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can take full advantage of the rich programming patterns provided by the common language runtime include aggregate functions, stored procedures, triggers, and types. Here is an example of sql server 2005 using the clr function to compress ntext type fields:

Vs2005 creates a new database project for the data.

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; using System.IO.Compression; using System.Text; public partial class Gzip {[Microsoft.SqlServer.Server.SqlFunction] public static SqlChars GzipToString (SqlBytes gBytes) {byte [] bytes = gBytes.Value; bytes = Decompress (bytes); string str = Encoding.GetEncoding (936) .GetString (bytes); SqlChars sqlchars = new SqlChars (str); return (sqlchars) } [Microsoft.SqlServer.Server.SqlFunction] public static SqlBytes StringToGzip (SqlChars chars) {byte [] bytes = Encoding.GetEncoding. GetBytes (chars.Buffer); bytes = Compress (bytes); SqlBytes gBytes = new SqlBytes (bytes); return (gBytes) } # region uses .net system with Gzip compression classes to compress data / summary > / / param > / / returns > public static byte [] Compress (byte [] data) {byte [] bData; MemoryStream ms = new MemoryStream (); GZipStream stream = new GZipStream (ms, CompressionMode.Compress, true); stream.Write (data, 0, data.Length); stream.Close (); stream.Dispose () / / the stream stream must be closed to return Ms stream data, otherwise the data will be incomplete / / and 0 bData = ms.ToArray (); ms.Close (); ms.Dispose (); return bData;} / decompress data / summary > / / param > / / returns > public static byte [] Decompress (byte [] data) {byte [] bData will be returned when decompressing the data (buffer, 0, buffer.Length) MemoryStream ms = new MemoryStream (); ms.Write (data, 0, data.Length); ms.Position = 0; GZipStream stream = new GZipStream (ms, CompressionMode.Decompress, true); byte [] buffer = new byte [1024]; MemoryStream temp = new MemoryStream (); int read = stream.Read (buffer, 0, buffer.Length); while (read > 0) {temp.Write (buffer, 0, read); read = stream.Read (buffer, 0, buffer.Length) } / / the stream stream must be closed to return Ms stream data, otherwise the data will be incomplete stream.Close (); stream.Dispose (); ms.Close (); ms.Dispose (); bData = temp.ToArray (); temp.Close (); temp.Dispose (); return bData;} # endregion}

Add a varbinary (MAX) field to the database, transfer the compressed field over and delete the original field.

Then use these two of the clr function as follows

Select: SELECT top 10 dbo.GzipToString ([content1]) FROM [content_02] insert: insert into [content_02] ([content1]) values (dbo.StringToGzip ('123abc')) after reading this article, I believe you have some understanding of "how CLR functions compress NTEXT type fields". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!