Skip to content

Instantly share code, notes, and snippets.

Embed
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
You can’t perform that action at this time.