In addition, you can save it as compressed data in your table and decompress it in the fly before displaying it to the end users. However, we can save the compressed data for the application that will read it, decompress it and display the result for the end users. Reading the compressed data and displaying it for the end users has no practical purposes, as this binary data is unintelligible for them. You can derive from the execution plans below that the weight of reading the compressed data is 38%, although it performed a table scan, compared to reading from the original table that consumes 62% of the overall load: The IO statistics show us that the logical reads required to read the data from the compressed table is about 0.3 of the logical reads required to read from the original table: Wow! The time required to retrieve the data from the compressed table is about 32% of the time required to retrieve the data from the original table: If you try to run the below simple T-SQL statement that compresses the provided text using SQL Server 2014 version instance: No special preparation is required to use the COMPRESS and DECOMPRESS functions, just the SQL Server instance that you use should be SQL Server 2016 version. Let us start our practical demo that describes how we can use these two new built-in functions and the performance gain from using it. Moreover, you have the choice to compress it at SQL or the application side and decompress it at the second side, using the same GZIP compressing algorithm.
If you have an already compressed binary data such as jpeg and pdf, you will spend more CPU cycles without taking benefits from a good compression ratio. You can benefit from the COMPRESS and DECOMPRESS functions in compressing big text columns, such as VARCHAR(MAX) or NVARCHAR(MAX) in addition to the large binary data, since the old compression technology at the page and row levels don’t work well with such data types. You may ask yourself, where we can use these new built-in functions. You should explicitly cast the Decompress function result to the required data type in order get a readable result. Decompress function returns a value with varbinary(max) data type, representing the decompressed value of the binary input argument using the GZIP algorithm.
Where the Expressions can be any value with varbinary(n), varbinary(max), or binary(n) data types. The result of the Compression function is a byte array of varbinary(max) data type representing the compressed content of the input value using the GZIP compression algorithm.Īlso, the basic syntax for the DECOMPRESS built-in function is: Where the Expressions can be any value with nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) data types. The basic syntax for the COMPRESS built-in function is: In this way, you can easily decompress the data that is compressed at the application side in your SQL Server instance or decompress the data that is compressed at the SQL Server side in your application, as both are using the same standard algorithm. The Compress and Decompress functions use the Standard GZIP compression algorithm to compress the data itself. Starting from SQL Server 2016, an evolution occurred on the data compression technique that were used in the previous versions, by introducing two new built-in functions for compressing and decompressing data, which also available in all SQL Server 2016 editions, opposite to the page and row level compression that is available only in the Enterprise edition. SQL Server 2016 version keeps with the tradition of surprising us with the new features, enhancements and built-in functions. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The concept of data compression is not a new on for SQL Server Database Administrators, as it is was introduced the first time in SQL Server 2008.
0 kommentar(er)
