COMPRESS-function.sql

/***********************************************************************
Copyright 2017, SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/

/* Doorstop*/
RAISERROR('Did you mean to run the whole thing?', 20,1) WITH LOG;
GO


/**********************************
Recreate database 
**********************************/
USE master;
GO

IF DB_ID('CompressionTest') IS NOT NULL
BEGIN
	ALTER DATABASE CompressionTest
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

	DROP DATABASE CompressionTest;
END

CREATE DATABASE CompressionTest;
GO


ALTER DATABASE [CompressionTest] MODIFY FILE 
    ( NAME = N'CompressionTest_log', SIZE=2GB, MAXSIZE = 5GB, FILEGROWTH =  256MB )
GO
ALTER DATABASE [CompressionTest] MODIFY FILE 
    ( NAME = N'CompressionTest', SIZE=5GB, FILEGROWTH = 256MB)
GO


ALTER DATABASE [CompressionTest] SET QUERY_STORE = ON
GO
ALTER DATABASE [CompressionTest] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 512)
GO



USE CompressionTest;
GO

CREATE TABLE dbo.NotCompressed (
    NotCompressedId INT IDENTITY(1,1) NOT NULL,
    Col1 nvarchar(10) default ('stuff'),
    Notes NVARCHAR(MAX) NULL,
    CONSTRAINT NotCompressedPK_NotCompressedId PRIMARY KEY CLUSTERED (NotCompressedId)
);


--This query adapted from pattern attributed 
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
), 
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b), 
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2), 
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3), 
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4), 
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.NotCompressed (Notes) 
SELECT TOP (10000000) 
        CASE WHEN 0 =
            (ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 100
        THEN 
        REPLICATE(CAST('sql' AS VARCHAR(MAX)),(ROW_NUMBER() OVER (ORDER BY (SELECT 0)))/100.)
        ELSE NULL
        END
FROM e6;


--Create a copy of the table
CREATE TABLE dbo.Compressed (
    CompressedId INT IDENTITY(1,1) NOT NULL,
    Col1 nvarchar(10) default ('stuff'),
    Notes VARBINARY(MAX) NULL,
    CONSTRAINT CompressedPK_NotCompressedId PRIMARY KEY CLUSTERED (CompressedId)
);
GO


--This query adapted from pattern attributed 
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
), 
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b), 
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2), 
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3), 
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4), 
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.Compressed (Notes) 
SELECT TOP (10000000) 
        CASE WHEN 0 =
            (ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 100
        THEN 
        COMPRESS(REPLICATE(CAST('sql' AS VARCHAR(MAX)),(ROW_NUMBER() OVER (ORDER BY (SELECT 0)))/100.))
        ELSE NULL
        END
FROM e6;





--Compare
SELECT
	si.index_id,
	si.name as index_name,
	si.fill_factor,
	si.is_primary_key,
	ps.reserved_page_count * 8./1024. as reserved_MB,
	ps.lob_reserved_page_count * 8./1024. lob_reserved_MB,
	ps.row_count
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes si on ps.object_id=si.object_id and ps.index_id=si.index_id
JOIN sys.objects so on si.object_id=so.object_id
JOIN sys.schemas sc on so.schema_id=sc.schema_id
WHERE sc.name='dbo' and
    so.name in ('NotCompressed','Compressed')
GO



--Query against the base table
SELECT
	CompressedId,
    DECOMPRESS(Notes) AS decompressed_only,
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)) AS correct_conversion,
	CAST(DECOMPRESS(Notes) AS NVARCHAR(MAX)) AS incorrect_conversion
FROM dbo.Compressed
WHERE
    Notes is not null
	--CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)) = replicate('sql',100)
GO


--We can't do this
CREATE INDEX ix_indextest 
on dbo.Compressed (Notes)
GO

--We can create a filtered index with it...
CREATE INDEX ix_filtertest 
on dbo.Compressed (CompressedId)
    INCLUDE (Notes)
WHERE (Notes IS NOT NULL);
GO

--But it doesn't think it's safe to use the index...
SELECT
	CompressedId,
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX))
FROM dbo.Compressed WITH (INDEX (ix_filtertest))
WHERE
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)) = replicate('sql',100)
GO


--Add "InevitableLOBColumnCompressed IS NOT NULL" to the query...
SELECT
	CompressedId,
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX))
FROM dbo.Compressed WITH (INDEX (ix_filtertest))
WHERE
    Notes IS NOT NULL and
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)) = replicate('sql',100)
GO


SELECT
	CompressedId,
	CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)),
    DATALENGTH(CAST(DECOMPRESS(Notes) AS VARCHAR(MAX)))
FROM dbo.Compressed WITH (INDEX (ix_filtertest))
WHERE
    Notes IS NOT NULL