Columnstore,
from the inside

Rowstore: array of structs

// A row is contiguous. Loading one row is cheap.
record Order(
    int Id, DateOnly Date, int CustomerId,
    decimal Amount, string Status);

Order[] orders;

// SUM(Amount) touches Id, Date, CustomerId and
// Status too — they sit between the values you want.
decimal total = 0;
foreach (var o in orders) total += o.Amount;

Columnstore: struct of arrays

// A column is contiguous. Loading one column is cheap.
class OrderColumns {
    int[]      Id;
    DateOnly[] Date;
    int[]      CustomerId;
    decimal[]  Amount;
    string[]   Status;
}

// SUM(Amount) touches exactly one array.
decimal total = amountColumn.Sum();

• The row is the atom. To read Amount you pull the whole page containing the whole row, including the forty columns you did not ask for.
• Compression options are ROW and PAGE. PAGE gets prefix and dictionary compression within a single page — a tiny window, so typical gains are 2–4×.
• Reads are page-granular, so a scan of a 40-column table costs the same whether your query needs 1 column or 40.
• This is exactly the right shape for OLTP: fetch the order, update the order, lock the order.

• Elimination is only as good as your data order. Insert order determines rowgroup boundaries; a CCI does not sort for you.
• Numeric, date and time types eliminate reliably. SQL Server 2022 and Azure SQL extended elimination to string, binary, uniqueidentifier and datetimeoffset with scale > 2.
• Partitioning still helps: partition elimination happens first, then rowgroup elimination inside surviving partitions.
• Two caveats on the 2022 string support: an existing index only benefits after a rebuild (ALTER INDEX REBUILD or DROP_EXISTING = ON), and elimination works for LIKE 'abc%' but never for LIKE '%abc'.
• Segment elimination never applies to LOB types — the (max) lengths.

-- what the optimiser is reading before it reads data
SELECT c.name, s.segment_id, s.row_count,
       s.min_data_id, s.max_data_id, s.on_disk_size
FROM sys.column_store_segments s
JOIN sys.partitions p ON p.hobt_id = s.hobt_id
JOIN sys.columns  c ON c.object_id = p.object_id
                     AND c.column_id = s.column_id
WHERE p.object_id = OBJECT_ID(N'dbo.Sales')
  AND c.name = N'OrderDate'
ORDER BY s.segment_id;

Up to SQL Server 2017 you had to run REORGANIZE yourself to merge small compressed rowgroups and reclaim deleted space. Microsoft now says index quality is maintained automatically on SQL Server 2019+, Azure SQL Database and MI, removing the need for periodic maintenance in most cases — but it is not instant, and it will not rescue a badly shaped load pattern.

• Aggregate pushdown covers MIN, MAX, SUM, COUNT, COUNT(*) — not distinct aggregates, not string columns, not virtual columns, and input and output types must fit in 64 bits. The aggregate must sit directly on the scan, with or without GROUP BY.
• String predicate pushdown uses the segment dictionary: match the predicate against the dictionary once, then compare integer ids for the million rows. It does not apply to delta rowgroups (no dictionary), or when a dictionary exceeds 64K entries.
• Bitmap filters from a hash join get pushed into the scan, so the fact-table scan skips non-joining rows before they are ever produced.

• Adaptive joins (2017+/Azure) defer the hash-vs-loop decision until the build input is materialised — useful when a report's selectivity varies by parameter.
• Memory grant feedback corrects an over- or under-sized grant on subsequent executions, which matters because batch mode hash tables want real memory.
• Anything the scan cannot push down becomes a residual filter above it. That is fine, just visible as a bigger row count leaving the scan.
• Microsoft's two pages disagree on AVG: the query-performance reference omits it, the what's-new page lists it. Treat AVG as unconfirmed and check your own plan.

• Actual Execution Mode on the scan. If it says Row, you lost batch mode — usually a scalar UDF, a cursor, or a data type the operator will not vectorise.
• Storage property on the scan: ColumnStore means the index was used; RowStore means the optimiser picked a B-tree instead.
• Segments read vs segments skipped. Skipped near zero means elimination is not working — your data is not ordered by the predicate column.
• Locally aggregated rows on the scan is the fingerprint of aggregate pushdown having fired.
• In STATISTICS IO, look at lob logical reads. Columnstore traffic shows up there, not in logical reads.

-- the single most useful columnstore query you will run
SELECT i.name                          AS index_name,
       rg.partition_number,
       rg.row_group_id,
       rg.state_desc,                   -- OPEN / CLOSED / COMPRESSED
       rg.total_rows,
       rg.deleted_rows,
       rg.trim_reason_desc,             -- why it closed early
       rg.size_in_bytes / 1048576.0   AS size_mb
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
JOIN sys.indexes AS i
  ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE rg.object_id = OBJECT_ID(N'dbo.Sales')
ORDER BY rg.partition_number, rg.row_group_id;

-- keep the hot rows out of the columnstore entirely
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
    ON dbo.Orders (OrderDate, CustomerId, Region, Amount)
    WHERE Status IN (N'Shipped', N'Closed');

-- and the other direction: a B-tree on a CCI table,
-- supported since SQL Server 2016
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_OrderId
    ON dbo.Sales (OrderId);

• A filtered NCCI on cold rows only is the classic HTAP pattern — churn happens on hot rows, which the index never sees, so maintenance cost stays low.
• COMPRESSION_DELAY is the other HTAP knob: it holds frequently-changing rows in the delta store for N minutes so they stabilise before being compressed, instead of being compressed and then immediately tombstoned.
• An NCCI is a second copy of its columns, so it costs storage and write amplification like any index. It just compresses far better than a B-tree copy would.
• Since SQL Server 2016 a CCI table can also carry nonclustered B-tree indexes and a primary key, which buys back seeks.

-- merge small + heavily deleted rowgroups, online, cheap
ALTER INDEX CCI_Sales ON dbo.Sales
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- full rewrite of one partition: best quality, most cost
ALTER INDEX CCI_Sales ON dbo.Sales
    REBUILD PARTITION = 7 WITH (ONLINE = ON, MAXDOP = 1);

-- guarantee elimination: sort at build time (Azure SQL DB,
-- Azure SQL MI on the always-up-to-date policy, SQL Server 2022+)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales ON dbo.Sales
    ORDER (OrderDate)
    WITH (DROP_EXISTING = ON, MAXDOP = 1);

• REORGANIZE always runs online and costs less than a rebuild — reach for it first. REBUILD is the sledgehammer; scope it to a partition.
• ONLINE = ON for columnstore create and rebuild arrived in SQL Server 2022, and is available on Azure SQL Database and MI. It is not available on SQL Server 2019 or earlier.
• MAXDOP = 1 on an ordered build stops parallel streams interleaving and destroying the sort. It is slower, and worth it.
• Retention by partition switch, never by DELETE.

• A B-tree optimises finding a row. A columnstore optimises summarising a column. Every other difference follows from that.
• Three multipliers compound: fewer rowgroups (elimination), fewer columns (projection), fewer bytes per value (compression). Then batch mode removes the per-row CPU tax on whatever survives.
• The delta store exists because compressed segments are immutable. Everything awkward about columnstore writes traces back to that one fact.
• 102,400 rows per batch and 1,048,576 rows per rowgroup are the two constants worth memorising.
• Elimination depends on data order, and a CCI will not sort for you. Load in order, or build an ordered index.
• It is not either/or: NCCI on an OLTP table, or nonclustered B-trees on a CCI table, get you both shapes.