Columnstore
Indexes
·
In-memory, compressed data in pages based on
columns instead of rows
Types of
Columnstore Index
• Clustered
Columnstore Indexes
– SQL
Server 2014 Enterprise, Developer, and Evaluation Edition Only
– Includes
all columns in the table
– Only
index on the table
– Optimizes
storage for compression and performance
– Updatable
CREATE CLUSTERED COLUMNSTORE INDEX
[IX_CS_FactProductInventory]
ON dbo. FactProductInventory;
• Non-Clustered
Columnstore Indexes
– Includes
some or all columns in the table
– Can
be combined with other indexes
– Read-Only
CREATE
NONCLUSTERED COLUMNSTORE INDEX [IX_NCS_FactProductInventory]
ON dbo. FactProductInventory
(ProductKey, DateKey, UnitCost);
Updating
Clustered Columnstores
• A row-based deltastore stores
interim changes
– New rows are inserted into the
deltastore until the minimum rowgroup size is reached.
– Deleted rows in the deltastore are
physically deleted. Columnstore data is marked as deleted, and space is
reclaimed when the index is rebuilt.
– Updated rows in the deltastore are
modified. Columnstore data is marked as
deleted and a new row is added to the deltastore.
Updating
Nonclustered Columnstores
• Drop and recreate columnstore
indexes
• Partition the table and switch in
new rows
– Switched table must have a matching
columnstore index
• Use Trickle Updating:
– Store only static data in the
columnstore table
– Create a matching delta table for
dynamic data rows
– Use UNION or Common Table
Expressions to retrieve data
– Periodically move stabilized rows
from the delta table to the columnstore table using one of the above techniques
See Delta Store
SELECT o.Name As TableName, c.index_id, c.row_group_id,
c.state_description, c.total_rows
FROM sys.sysobjects o JOIN Sys.Column_Store_Row_Groups c ON
o.id = c.object_id
https://www.mssqltips.com/sqlservertip/4158/sql-server-columnstore-btree-and-hybrid-index-performance-comparison-for-data-warehouses/
Comments
Post a Comment