关于索引认知的技术确认：我的理解是否存在遗漏或错误？

First off, your core understanding is solid—you’ve nailed the high-level distinctions between rowstore and columnstore, and clustered vs non-clustered. Let’s refine your points and fill in critical gaps to make this even more precise:

Corrections to Your Original Notes #

• Clustered Rowstore Index

  • Your points about 1 per table, ordered data storage, and primary keys defaulting to clustered are spot-on.
  • Key missing detail: The leaf nodes of a clustered index are the actual table data rows (not pointers). If a table has no clustered index, it’s stored as a Heap—an unordered collection of rows with no inherent order.
  • Also: If no primary key is defined, SQL Server will use the first unique, non-null index as the clustered index; if none exist, the table remains a Heap.

• Non-Clustered Rowstore Index

  • Correction: SQL Server allows up to 1000 non-clustered indexes per table (not 999) in recent versions.
  • Clarification: Leaf nodes contain either the clustered index key (for tables with a clustered index) or a Row ID (RID, for Heaps) to locate the full data row.
  • Missing feature: Non-clustered indexes can be covering indexes—they include non-key columns to avoid "bookmark lookups" to the base table, which drastically speeds up queries that only need those specific columns.

• Columnstore Indexes

  • Critical correction: You can create multiple non-clustered columnstore indexes on a single table (only the clustered columnstore index is limited to one per table). Non-clustered columnstore indexes are perfect for adding analytical capabilities to OLTP tables without altering the base rowstore structure.
  • Additional detail: Columnstore indexes use batch mode execution (processing 10k rows at a time) which is far faster than rowstore’s row-by-row mode for large aggregations or scans.

• Clustered Columnstore Indexes

  • Correction: As of SQL Server 2016, you can create non-clustered rowstore indexes on a clustered columnstore table—these are ideal for speeding up point queries (e.g., looking up a single order by ID) which columnstore isn’t optimized for.
  • Clarification: You can’t directly define a primary key on a clustered columnstore table, but you can enforce uniqueness by creating a unique non-clustered rowstore index on the key columns, which acts like a primary key for practical purposes.

Clearer Distinctions & Use Cases #

Let’s map each index type to its sweet spot and limitations:

Rowstore Indexes (Clustered + Non-Clustered) #

• What they do: Store data row-by-row in B-tree structures, optimized for fast single-row or small-range lookups.
• Best for:
  • OLTP workloads (e.g., transactional systems where you’re inserting/updating/deleting individual rows frequently).
  • Dimension tables in data warehouses (smaller tables with frequent point queries).
  • Queries that need to retrieve most columns of a row.
• Limitations:
  • Poor compression compared to columnstore—uses more storage.
  • Slow for large-scale aggregations (e.g., SUM/COUNT over millions of rows).

Columnstore Indexes (Clustered + Non-Clustered) #

• What they do: Store data column-by-column with high compression, optimized for batch processing of large datasets.
• Best for:
  • Fact tables in data warehouses (massive tables with millions/billions of rows).
  • Analytical queries that only need a few columns (e.g., aggregating sales by region over a year).
  • Reporting workloads with large scans and aggregations.
• Limitations:
  • Not ideal for frequent small updates/inserts (though recent versions have improved batch insert performance).
  • Non-clustered columnstore indexes add overhead to OLTP write operations.

Clustered vs Non-Clustered (Rowstore) #

• Clustered: Defines the physical order of the table data—leaf nodes = data rows. Only 1 per table.
• Non-Clustered: Separate B-tree that points to the base table data—used to speed up queries on non-clustered columns. Up to 1000 per table.

Clustered Columnstore vs Non-Clustered Columnstore #

• Clustered: Replaces the table’s storage structure (no separate heap/clustered rowstore). Only 1 per table. Best for large fact tables where analytical queries are the primary workload.
• Non-Clustered: Added on top of a heap or clustered rowstore table. Lets you keep OLTP performance while adding analytical capabilities for specific columns.

Final Check #

Your original summary had most of the big picture right—key misses were non-clustered columnstore indexes, covering non-clustered rowstore indexes, and the ability to add non-clustered rowstore indexes to clustered columnstore tables. The correction about non-clustered index count and clustered columnstore primary key workarounds are also important for real-world accuracy.

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles