Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
/***********************************************************************
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment