Columnstore Indexes

Rowstore (B-tree leaf page):
  [col1, col2, col3, col4] row 1
  [col1, col2, col3, col4] row 2
  ...

Columnstore rowgroup:
  segment for col1: [val1, val1, val1, val2, ...]  ← high compression (similar values)
  segment for col2: [val2, val2, val3, val3, ...]
  ...

-- Create a table with CCI as primary storage
CREATE TABLE dbo.FactSales
(
    SaleID       INT           NOT NULL,
    ProductID    INT           NOT NULL,
    CustomerID   INT           NOT NULL,
    SaleDate     DATE          NOT NULL,
    Quantity     INT           NOT NULL,
    UnitPrice    DECIMAL(10,2) NOT NULL,
    TotalAmount  DECIMAL(12,2) NOT NULL
)
WITH (DATA_COMPRESSION = COLUMNSTORE);  -- explicit; CCI implies columnstore compression

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

-- Or combined:
CREATE TABLE dbo.FactSales2
(
    SaleID    INT  NOT NULL,
    SaleDate  DATE NOT NULL,
    Amount    DECIMAL(12,2) NOT NULL,
    INDEX CCI_FactSales2 CLUSTERED COLUMNSTORE
);

-- Drop existing clustered index and replace with CCI
-- (This rebuilds the entire table — schedule appropriately)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_MyTable
ON dbo.MyTable
WITH (DROP_EXISTING = ON, ONLINE = ON);  -- ONLINE requires Enterprise

-- CCI table with an added B-tree index for point lookups
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

CREATE NONCLUSTERED INDEX IX_FactSales_SaleID
ON dbo.FactSales (SaleID);  -- for single-row lookups by primary key

-- Add a read-only analytics index to an OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON dbo.Orders (OrderDate, CustomerID, ProductID, Quantity, TotalAmount)
WHERE OrderDate >= '2020-01-01';  -- optional filtered NCCI

-- Good: analytics columns only
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
ON dbo.Orders (OrderDate, RegionID, ProductID, Revenue, Cost);

-- Wasteful: includes all 40 columns — hurts compression, increases memory
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Wide
ON dbo.Orders;  -- all columns = heap-like, defeats the purpose

INSERT (< 1,048,576 rows threshold)
         │
         ▼
  ┌─────────────┐
  │  Delta store │  (open B-tree rowstore, not yet compressed)
  │  (OPEN state)│
  └──────┬──────┘
         │ delta store fills to ~1M rows or REORGANIZE/REBUILD triggered
         ▼
  ┌─────────────┐
  │  Delta store │
  │ (CLOSED state)│  ← ready for compression but still B-tree
  └──────┬──────┘
         │ tuple mover background thread (runs ~5-min intervals)
         ▼
  ┌──────────────────────┐
  │  Compressed row group │  ← column segments, dictionary, bitmaps
  │  (COMPRESSED state)  │
  └──────────────────────┘

-- REORGANIZE compresses all CLOSED delta stores immediately (no full rebuild)
ALTER INDEX CCI_FactSales ON dbo.FactSales REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON);

SELECT
    OBJECT_NAME(i.object_id)     AS table_name,
    i.name                        AS index_name,
    rg.state_description,
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats rg
JOIN sys.indexes i ON rg.object_id = i.object_id AND rg.index_id = i.index_id
WHERE OBJECT_NAME(i.object_id) = 'FactSales'
ORDER BY rg.row_group_id;

-- Detect suboptimal (trim) row groups
SELECT
    OBJECT_NAME(object_id) AS table_name,
    index_id,
    state_description,
    total_rows,
    deleted_rows,
    (total_rows - deleted_rows) AS live_rows,
    size_in_bytes / 1024.0 / 1024.0 AS size_mb,
    CASE
        WHEN state_description = 'COMPRESSED' AND total_rows < 900000
        THEN 'SUBOPTIMAL - consider REBUILD'
        ELSE 'OK'
    END AS assessment
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE OBJECT_NAME(object_id) = 'FactSales'
ORDER BY row_group_id;

-- Minimum 102,400 rows per batch for direct-path compression
BULK INSERT dbo.FactSales
FROM 'C:\data\sales.csv'
WITH (
    BATCHSIZE = 1048576,  -- 1M rows → full row groups
    TABLOCK,              -- required for minimal logging + direct path
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

-- Or via INSERT ... SELECT with TABLOCK
INSERT INTO dbo.FactSales WITH (TABLOCK)
SELECT * FROM dbo.FactSales_Staging;  -- must be > 102,400 rows per insert

Compressed row group #1: OrderDate segment min=2020-01-01, max=2020-03-31
Compressed row group #2: OrderDate segment min=2020-04-01, max=2020-06-30
Compressed row group #3: OrderDate segment min=2020-07-01, max=2020-09-30

Query: WHERE OrderDate BETWEEN '2020-04-15' AND '2020-06-15'
→ Only row group #2 is scanned. Row groups #1 and #3 are eliminated.

-- Load in date order to maximize segment elimination on OrderDate
INSERT INTO dbo.FactSales WITH (TABLOCK)
SELECT *
FROM dbo.FactSales_Staging
ORDER BY OrderDate;  -- sort before insert

-- Or use REORGANIZE after load to re-sort within row groups (partial help)
ALTER INDEX CCI_FactSales ON dbo.FactSales REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- 2022+ ordered CCI (if available in your build)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales
ORDER (SaleDate);

-- DMV: segment statistics per object
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.column_id,
    c.name AS column_name,
    s.segment_id,
    s.min_data_id,
    s.max_data_id,
    s.row_count,
    s.on_disk_size
FROM sys.column_store_segments s
JOIN sys.columns c ON c.object_id = s.object_id AND c.column_id = s.column_id
WHERE OBJECT_NAME(s.object_id) = 'FactSales'
ORDER BY s.column_id, s.segment_id;

-- Typical star-schema query — designed to leverage columnstore
SELECT
    d.CalendarYear,
    p.ProductCategory,
    r.RegionName,
    SUM(f.Quantity)    AS TotalUnits,
    SUM(f.TotalAmount) AS TotalRevenue,
    AVG(f.UnitPrice)   AS AvgPrice
FROM dbo.FactSales f
JOIN dbo.DimDate    d ON f.SaleDateKey = d.DateKey
JOIN dbo.DimProduct p ON f.ProductID   = p.ProductID
JOIN dbo.DimRegion  r ON f.RegionID    = r.RegionID
WHERE d.CalendarYear BETWEEN 2021 AND 2023
  AND p.ProductCategory = 'Electronics'
GROUP BY d.CalendarYear, p.ProductCategory, r.RegionName
ORDER BY d.CalendarYear, TotalRevenue DESC;

-- Window aggregate — can use batch mode in 2019+
SELECT
    SaleDate,
    TotalAmount,
    SUM(TotalAmount) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM dbo.FactSales
WHERE SaleDate >= '2023-01-01';

-- NCCI on OLTP table for real-time analytics
-- Base table: clustered B-tree on OrderID (OLTP access pattern)
-- NCCI: analytics columns only

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON dbo.Orders (OrderDate, CustomerID, ProductID, Quantity, TotalAmount, Status);

-- OLTP query — uses clustered B-tree
SELECT OrderID, CustomerID, Status
FROM dbo.Orders
WHERE OrderID = 12345678;

-- Analytics query — optimizer chooses NCCI
SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    SUM(TotalAmount) AS Revenue
FROM dbo.Orders
WHERE Status = 'Completed'
GROUP BY YEAR(OrderDate), MONTH(OrderDate);

-- Check delete ratio
SELECT
    OBJECT_NAME(object_id) AS table_name,
    index_id,
    state_description,
    total_rows,
    deleted_rows,
    CAST(deleted_rows * 100.0 / NULLIF(total_rows, 0) AS DECIMAL(5,1)) AS pct_deleted
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE OBJECT_NAME(object_id) = 'FactSales'
  AND state_description = 'COMPRESSED'
ORDER BY pct_deleted DESC;

-- Step 1: Prepare staging table with same structure + CCI
CREATE TABLE dbo.FactSales_Staging
(
    SaleID      INT  NOT NULL,
    SaleDate    DATE NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL,
    INDEX CCI_Staging CLUSTERED COLUMNSTORE
);

-- Step 2: Load and compress staging data (full row groups)
INSERT INTO dbo.FactSales_Staging WITH (TABLOCK)
SELECT * FROM dbo.FactSales_Raw_Load
ORDER BY SaleDate;  -- sort for segment elimination

ALTER INDEX CCI_Staging ON dbo.FactSales_Staging
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Step 3: Switch partition into main table
ALTER TABLE dbo.FactSales_Staging
SWITCH TO dbo.FactSales PARTITION @NewPartitionNumber;

-- Light maintenance: compress delta stores after incremental load
ALTER INDEX CCI_FactSales ON dbo.FactSales REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Heavy maintenance: full rebuild (monthly or when >20% deleted rows)
ALTER INDEX CCI_FactSales ON dbo.FactSales REBUILD
WITH (ONLINE = ON, MAXDOP = 4);  -- ONLINE requires Enterprise

-- Manually update statistics after REORGANIZE
UPDATE STATISTICS dbo.FactSales WITH FULLSCAN;

-- Estimate columnstore compression savings
EXEC sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'FactSales',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'COLUMNSTORE';

-- Also check COLUMNSTORE_ARCHIVE for rarely accessed cold data
EXEC sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'FactSales',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'COLUMNSTORE_ARCHIVE';

-- Apply archive compression to old partitions
ALTER INDEX CCI_FactSales ON dbo.FactSales REBUILD
PARTITION = 1  -- old/cold partition
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Bad: CCI table, single-row lookup — full row group must be decompressed
SELECT * FROM dbo.FactSales WHERE SaleID = 12345678;

-- Better: Add a nonclustered B-tree index for point lookup
CREATE NONCLUSTERED INDEX IX_FactSales_SaleID ON dbo.FactSales (SaleID);
-- Optimizer will use the B-tree index for this query

-- This will FAIL if dbo.Orders has a Notes VARCHAR(MAX) column
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Orders ON dbo.Orders;
-- Error: Column 'Notes' has a data type that cannot participate in a columnstore index.

-- Workaround: NCCI on non-LOB columns only
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
ON dbo.Orders (OrderDate, CustomerID, Amount, Quantity);
-- Omit Notes column

-- Bad: scalar UDF in SELECT list forces row mode
SELECT dbo.FormatCurrency(TotalAmount), SaleDate  -- row mode, slow
FROM dbo.FactSales;

-- Good: inline the logic or use an iTVF
SELECT FORMAT(TotalAmount, 'C', 'en-US'), SaleDate  -- batch mode eligible
FROM dbo.FactSales;

SELECT
    session_id,
    granted_memory_kb,
    used_memory_kb,
    ideal_memory_kb,
    is_small,
    query_cost
FROM sys.dm_exec_query_memory_grants
WHERE session_id = @@SPID;

-- Segment elimination works for direct predicates
WHERE SaleDate >= '2023-01-01'           -- ✓ segment elimination

-- Segment elimination does NOT help with negation
WHERE SaleID NOT IN (SELECT SaleID FROM dbo.Returns)  -- ✗ full scan

-- Enable incremental stats on CCI fact table
CREATE STATISTICS ST_FactSales_SaleDate
ON dbo.FactSales (SaleDate)
WITH INCREMENTAL = ON;

-- Update only the latest partition
UPDATE STATISTICS dbo.FactSales ST_FactSales_SaleDate
WITH RESAMPLE ON PARTITIONS (24);  -- only partition 24