Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE TABLE dbo.testColumnstoreBit (X BIT NOT NULL, Y BIT NOT NULL)
GO
-- Generate two bits, one with all 0 values and one with pseudo-random data
INSERT INTO dbo.testColumnstoreBit WITH (TABLOCK) (x, y)
SELECT TOP 1000000 0 AS x, CRYPT_GEN_RANDOM(2, CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS VARBINARY)) % 2 AS y
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO
EXEC sp_spaceused testColumnstoreBit
-- 10896 KB reserved, 10872 KB data
GO
CREATE CLUSTERED COLUMNSTORE INDEX cs_testColumnstoreBit ON testColumnstoreBit
GO
-- The BIT data compresses to just 1.3% of its original size
EXEC sp_spaceused testColumnstoreBit
-- 144 KB reserved, 32KB data
GO
SELECT s.on_disk_size, *
FROM sys.partitions p
JOIN sys.column_store_segments s
ON s.partition_id = p.partition_id
AND p.object_id = OBJECT_ID('dbo.testColumnstoreBit')
-- Both columns take up approximately the same amount of space: very, very little
-- I didn't look carefully, but assume
--1048 bytes
--1056 bytes
GO
DROP TABLE testColumnstoreBit
GO
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.