COMPRESS (Transact-SQL)
- Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).
DECOMPRESS (Transact-SQL)
- Decompress input expression using GZIP algorithm. Result of the compression is byte array (VARBINARY(MAX) type).
- Cast result to a target type if needed.
Example:
Drop temp table is exists:
IF OBJECT_ID('Tempdb.dbo.#TMP', 'U') IS NOT NULL
Begin
Drop Table #TMP;
End
Create temp table:
CREATE TABLE #TMP (
WebSiteID INT IDENTITY (1,1) PRIMARY KEY,
WebSiteURL VARBINARY(MAX),
WebsiteShortDescription VARBINARY(MAX),
WebsiteLongDescription VARBINARY(MAX),
WebsiteFullDescription VARBINARY(MAX)
)
GO
Declare & populate variables:
Declare @varWebSiteURL VARCHAR(MAX) = 'www.TechDevOps.com'
Declare @varWebsiteShortDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'
Declare @varWebsiteLongDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'
Declare @varWebsiteFullDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'
Check DATALENGTH of variables:
SELECT
DATALENGTH(@varWebSiteURL) as 'DATALENGTH @varWebSiteURL',
DATALENGTH(@varWebsiteShortDescription) as 'DATALENGTH @varWebsiteShortDescription',
DATALENGTH(@varWebsiteLongDescription) as 'DATALENGTH @varWebsiteLongDescription',
DATALENGTH(@varWebsiteFullDescription) as 'DATALENGTH @varWebsiteFullDescription'
Insert into temp table while compressing the data:
INSERT INTO #TMP (WebSiteURL,WebsiteShortDescription,WebsiteLongDescription,WebsiteFullDescription)
Select COMPRESS(@varWebSiteURL), COMPRESS(@varWebsiteShortDescription), COMPRESS(@varWebsiteLongDescription), COMPRESS(@varWebsiteFullDescription)
Check DATALENGTH of compressed table data:
SELECT DATALENGTH(WebSiteURL) as 'DATALENGTH WebSiteURL(Compressed)', WebSiteURL AS 'Compressed Data', CAST(DECOMPRESS(WebSiteURL) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteShortDescription) as 'DATALENGTH WebsiteShortDescription(Compressed)', WebsiteShortDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteShortDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteLongDescription) as 'DATALENGTH WebsiteLongDescription(Compressed)', WebsiteLongDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteLongDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteFullDescription) as 'DATALENGTH WebsiteFullDescription(Compressed)', WebsiteFullDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteFullDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
As you can see in the diagram above, the longer the string the better compression works!!!
Note:
These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side.
DATALENGTH()
- Returns the number of bytes used to represent any expression.
- DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
|
|
|
|
|
|