This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced the COMPRESS() and DECOMPRESS() function which we can use to compress column data of a table.
The Compression of the column is not a good always and not a good for all the column. For that, there are different ways for compression.
But we can use COMPRESS() on those columns which size are very big and which are not most in use.
The reason is, compressed column requires to be decompressed using DECOMPRESS() while selecting the data which will reduce query performance.
If we are facing a size related problem, we can think for COMPRESS() on those big size columns which are not most in use.
Please check the below demonstration on COMPRESS() and DECOMPRESS():
Create a sample table:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_DumpData ( ID INT ,RandomData NVARCHAR(MAX) ,CONSTRAINT pk_tbl_DumpData_ID PRIMARY KEY(ID) ) GO |
Insert dummy data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
;WITH CTE AS ( SELECT 1 ID UNION all SELECT ID + 1 FROM CTE WHERE ID + 1 <= 1000000 ) INSERT INTO tbl_DumpData(ID,RandomData) SELECT ID ,CONCAT(CAST(NEWID() AS NVARCHAR(MAX)), ' - I am follower of dbrnd.com') AS RandomData FROM CTE OPTION (MAXRECURSION 0) GO |
Create a sample table to store Compressed Data:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_DumpData_Compressed ( ID INT ,RandomData NVARCHAR(MAX) ,CONSTRAINT pk_tbl_DumpData_Compressed_ID PRIMARY KEY(ID) ) GO |
Insert Compressed data from the original table:
|
1 2 3 |
INSERT INTO tbl_DumpData_Compressed SELECT ID, COMPRESS (RandomData) AS RandomData FROM tbl_DumpData |
Compare the size of both table:
You will get to know that, compressed table size is smaller than original table
|
1 2 3 4 |
SP_Spaceused 'tbl_DumpData' GO SP_Spaceused 'tbl_DumpData_Compressed' GO |
Now, do DECOMPRESS and select your data:
|
1 2 3 4 |
SELECT ID ,CAST(DECOMPRESS(RandomData) AS NVARCHAR(MAX)) AS RandomData FROM tbl_DumpData_Compressed |
Leave a Reply