אינדקסי Columnstore ב-SQL Server: מדריך מעשי

??? ???? ?-warehouse ?????? ???? scans ?-aggregations ?????? ????? ??????.

CREATE TABLE dbo.FactSales
(
    SalesKey bigint NOT NULL,
    OrderDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    ProductKey int NOT NULL,
    Quantity int NOT NULL,
    NetAmount decimal(18,2) NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

????? ??? ?????? ???? ????? ?-OLTP-?? ????? ????? ?-rowstore, ??? ?????? ???? ????? ?????? ????.

CREATE TABLE dbo.SalesOrderDetail
(
    SalesOrderDetailID bigint NOT NULL PRIMARY KEY,
    SalesOrderID bigint NOT NULL,
    ProductID int NOT NULL,
    OrderQty int NOT NULL,
    UnitPrice money NOT NULL,
    ModifiedDate datetime2 NOT NULL
);

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrderDetail_Analytics
ON dbo.SalesOrderDetail (SalesOrderID, ProductID, OrderQty, UnitPrice, ModifiedDate);

????? ?? columnstore ????? ?????? ?-rowgroups, ?? ?? ???? ???? ???????.

SELECT
    OBJECT_NAME(object_id) AS table_name,
    row_group_id,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024.0 / 1024.0 AS size_mb
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSales')
ORDER BY row_group_id;