COMPRESS
and DECOMPRESS functions in Sql Server 2016
Sql Server 2016 introduced COMPRESS
and DECOMPRESS are the new built in functions.
COMPRESS and DECOMPRESS functions are
using the Standard GZIP algorithm. Values can be compressed and decompressed in
Sql server as well as in application. A Value also can be compressed in Sql
Server can be decompressed in the application layer and vice versa.
COMPRESS function compresses the
value and returns the binary data. Its return type is Varbinary (max).
DECOMPRESS function decompresses the
compressed binary data and returns the binary data of type Varbinary (max).
Remark:
- We need to explicitly cast the binary data of type
Varbinary (max) (decompressed output) to the native data type.
Example
1: Simple use of Compress and Decompress function
--------
COMPRESS function Example------------
SELECT COMPRESS ('Alok Kumar
Singh') AS 'OUTPUT'
OUTPUT:
- x1F8B080000000000040073CCC9CF56F02ECD4D2C5208CECC4BCF0000C4681A7A10000000
--------
DECOMPRESS function Example------------
SELECT
DECOMPRESS(0x1F8B080000000000040073CCC9CF56F02ECD4D2C5208CECC4BCF0000C4681A7A10000000) AS 'OUTPUT'
OUTPUT:
- 0x416C6F6B
--------
CASTING to DESIRE OUTPUT------------
SELECT CAST(0x416C6F6B204B756D61722053696E6768 as varchar(max))
OUTPUT:
- Alok Kumar Singh
Here
is the simple Compress and decompress function in SELECT Statement.
SELECT 'Code-view.com' AS ACTUAL_VALUE,
COMPRESS ('Alok Kumar
Singh') AS COMPRESSED_VALUE,
DECOMPRESS(COMPRESS ('Alok Kumar Singh'))
AS DECOMPRESSED_VALUE,
CAST(DECOMPRESS(COMPRESS ('Alok Kumar
Singh')) AS VARCHAR(MAX)) AS
CASTING_to_DESIRE_OUTPUT
OUTPUT:-
No comments:
Post a comment