---

title: SQL Server 2012列儲存索引技術

author: 風移

摘要

MS SQL Server 2012首次引入了列儲存索引（Columnstore Index）來加速資料分析（OLAP）和資料倉儲（Data Warehouse）場景的查詢，它主要是通過將資料按列壓縮儲存的方式來減少查詢對磁碟IOPS開銷和CPU開銷，最終達到提升查詢效率，降低響應時間的目的。當然，列儲存索引也不是一把萬能的鑰匙，在SQL Server 2012版本中它有諸多非常嚴苛限制條件。
這篇文章會從以下幾個方面來介紹列儲存索引：

列儲存索引所涉及到的基本概念

列儲存索引的結構

列儲存索引對查詢效能的影響

MS SQL Server 2012上列儲存索引的限制

解決列儲存索引表只讀問題

概念

首先讓我們來看看列儲存索引涉及到的幾個關鍵的概念。

列儲存技術

列儲存技術背後的核心思想並不是微軟首創的，早在上20世紀70年代，基於列的儲存系統就與傳統的行儲存資料庫管理系統一同出現了。微軟資料庫產品首次嘗試從傳統的行儲存結構轉變為面向列的儲存方案是在SQL Server2012這個產品，以期望這種方案能夠以最低限度的額外工作量換取更高的效能。

列儲存索引

MS SQL Server列儲存索引是使用列式資料格式（稱為列儲存）壓縮儲存、檢索和管理資料的技術。它主要目標是將盡量多的資料載入至記憶體中，在進行資料處理時，使用訪問記憶體的方式來替換從磁碟中讀取資料。這種處理方式有兩大優點，一是速度更快，二是硬碟的IOPS（每秒讀寫次數）消耗更低。

列儲存索引壓縮

資料壓縮對於MS SQL Server來說已經不是什麼新鮮玩意兒了，SQL Server支援資料庫備份壓縮，資料行壓縮和資料頁壓縮，當然列儲存索引壓縮預設是開啟的並且不允許禁用。相對於傳統按行儲存的結構來說，列儲存索引這種按列來儲存的特殊結構來說，壓縮更加有效。這是因為表中的相同列資料屬性相同，儲存的資料也非常相近，有可能還有非常多的重複值，因此資料壓縮比例更高，壓縮效率更快，更少的磁碟I/O開銷，資料讀取需要更少的記憶體，相同記憶體中可以儲存更多的資料。

使用下面語句，我們可以發現本文的測試表dbo.SalesOrder的列儲存索引NCSIX_ALL_Columns壓縮演算法是COLUMNSTORE。

USE ColumnStoreDB
GO
SELECT DISTINCT
    table_name = object_name(part.object_id)
    ,ix.name
    ,part.data_compression_desc 
FROM sys.partitions as part
    INNER JOIN sys.indexes as ix
    ON part.object_id = ix.object_id
    AND part.index_id = ix.index_id
WHERE part.object_id = object_id(`dbo.SalesOrder`,`U`)
    AND ix.name = `NCSIX_ALL_Columns`

結果如下：

Column Segment and Row Group

在列儲存索引中，SQL Server引入了兩個全新的概念：Column Segment和Row Group。

Column Segment：是SQL Server列儲存索引最基本的儲存單元，列儲存索引的每一列資料會被劃分為一個或者多個Column Segment。它是一組經過壓縮後物理儲存在相同儲存介質的列值。

Row Group：是一組同時被壓縮成列儲存格式的行，每一個Row Group中包含了每個列的一個Column Segment。Row Group定義了每一個Column Segment的列值。
以上的解釋還是非常抽象和難於理解，做一個圖，我們就很好理解什麼Column Segment和Row GROUP了。

Batch Mode Processing

在SQL Server OLAP的場景中，做BI類分析型查詢語句往往需要掃描非常大量的資料記錄數。Batch Mode Processing 是SQL Server新的查詢處理演算法，專門設計來高效地處理大量資料的批處理演算法，以加快統計分析類查詢的效率。其實這個演算法的原理實現非常簡單，SQL Server有一個專門的系統檢視sys.column_store_segments來存放列儲存索引的每個列的每個Segments的最小值和最大值，當SQL Server執行Batch Mode Processing查詢時，只需要和查詢篩選條件對比，就可以知道對應的Segment是否包含滿足條件的資料，從而可以實現快速的跳過哪些不滿足條件的Segment。由於每個Segment中包含成千上萬條記錄，所以SQL Server篩選出滿足條件的效率非常高，因此大大節約了磁碟I/O和因此帶來的CPU開銷。這種跳過不滿足條件Segment的演算法專業術語叫Segment Elimination。

USE ColumnStoreDB
GO
SELECT 
    table_name = object_name(part.object_id)
    ,col.name
    ,seg.segment_id
    ,seg.min_data_id
    ,seg.max_data_id
    ,seg.row_count
FROM sys.partitions as part
    INNER JOIN sys.column_store_segments as seg
    ON part.partition_id = seg.partition_id
    INNER JOIN sys.columns as col
    ON part.object_id = col.object_id
    AND seg.column_id = col.column_id
WHERE part.object_id = object_id(`dbo.SalesOrder`,`U`)
AND seg.column_id = 1
ORDER BY seg.segment_id

結果如下：

行列儲存結構對比

其實在列儲存索引引入SQL Server之前，SQL Server的索引我們通常不叫行儲存索引，而是叫B-Tree索引，因為SQL Server的行儲存索引是按照B-Tree結構來組織的。這一節我們來對比基於行儲存和基於列儲存的結構差異。

行儲存結構

在傳統的基於行儲存的結構中，表中每一行資料會儲存在一起。如果使用者需要其中的某個或者某幾個欄位的資料，SQL Server系統必須先將滿足條件的記錄所有欄位的值載入記憶體中，然後再從中篩選出使用者需要的列。換句話說，使用者的查詢語句橫向篩選是通過索引（傳統的B-Tree索引）來快速完成，而縱向列的篩選由於基於行儲存的設計而浪費了許多的系統效能，這些效能浪費包括載入過多列資料導致的記憶體浪費，磁碟I/O資源的浪費開銷，和因此而帶來的CPU開銷。那就讓我們看看基於行儲存的結構圖：

列儲存結構

為了解決行儲存結構導致資源浪費和多餘開銷，從MS SQL Server 2012開始，微軟引入了基於列儲存的新結構，具體使用在列儲存索引這個方面。列儲存結構是將資料按列來儲存，每一列的資料存放在一起。這樣當使用者在執行查詢的時候，可以快速拿到這一列的所有資料，而不會浪費多餘的IO資源和相應的CPU開銷。除了儲存結構的變化外，微軟還對列儲存索引預設啟用了資料壓縮功能，進一步減少了IO開銷。列儲存索引的結構如下：

行列儲存結構對比

以上是比較理論的認知，稍顯抽象，讓我們來看一個典型的例子，從具體的例子詳細分析基於行儲存和基於列儲存的資料獲取方式上的差異。
首先，讓我們來建立測試環境資料庫ColumnStoreDB，所需要使用到的表dbo.AutoType和dbo.SalesOrder，以及相應的資料初始化。為了照顧到後面《解決列儲存索引表只讀問題》章節，我將dbo.SalesOrder建立為分割槽表。

-- Create testing database
IF DB_ID(`ColumnStoreDB`) IS NULL
    CREATE DATABASE ColumnStoreDB;
GO

USE ColumnStoreDB
GO
IF OBJECT_ID(`dbo.AutoType`, `U`) IS NOT NULL
BEGIN
    DROP TABLE dbo.AutoType
END
GO

-- create demo table autoType
CREATE TABLE dbo.AutoType
(
   AutoID INT IDENTITY(101,1) NOT NULL PRIMARY KEY, 
   Make VARCHAR(20) NOT NULL,
   Model VARCHAR(20) NOT NULL,
   Color VARCHAR(15) NOT NULL,
   ModelYear SMALLINT NOT NULL
);

-- data init
INSERT INTO dbo.AutoType
SELECT `Ford`, `Explorer`, `white`, 2003 UNION ALL
SELECT `Satum`, `Lon`, `blue`, 2003 UNION ALL
SELECT `Lexus`, `GX460`, `gray`, 2010 UNION ALL
SELECT `Honda`, `CRV`, `blue`, 2007 UNION ALL
SELECT `Subaru`, `Legacy`, `green`, 2008 UNION ALL
SELECT `Honda`, `Civic`, `red`, 1996 UNION ALL
SELECT `Nissan`, `Sentra`, `silver`, 2012 UNION ALL
SELECT `Chevrolet`, `Tahoe`, `green`, 1995 UNION ALL
SELECT `Toyota`, `Celica`, `red`, 1992 UNION ALL
SELECT `BMW`, `X5`, `gray`, 2002 UNION ALL
SELECT `Subaru`, `Impreze`, `silver`, 2011 UNION ALL
SELECT `Volkswagen`, `Jetta`, `black`, 1995 UNION ALL
SELECT `Chevrolet`, `Impala`, `red`, 2008 UNION ALL
SELECT `Jeep`, `Liberty`, `gray`, 2012 UNION ALL
SELECT `Dodge`, `Dakota`, `blue`, 2000 
;

-- Create PARTITION FUNCTION & SCHEMA
CREATE PARTITION FUNCTION pf_SalesYear (datetime) 
AS RANGE LEFT FOR VALUES 
(`2013-01-01 00:00`, `2014-01-01 00:00`, `2015-01-01 00:00`, `2016-01-01 00:00`, `2017-01-01 00:00`, `2018-01-01 00:00`)
;
GO

CREATE PARTITION scheme ps_SalesYear
AS  PARTITION pf_SalesYear 
ALL TO ([PRIMARY])
;
GO

-- create demo table SalesOrder
IF OBJECT_ID(`dbo.SalesOrder`, `U`) IS NOT NULL
BEGIN
    DROP TABLE dbo.SalesOrder
END
GO
CREATE TABLE dbo.SalesOrder
(
    OrderID INT NOT NULL
    ,AutoID INT NOT NULL
    ,UserID INT NOT NULL
    ,OrderQty INT NOT NULL
    ,Price DECIMAL(8,2) NOT NULL
    ,UnitPrice AS Price * OrderQty
    ,OrderDate DATETIME NOT NULL
) ON ps_SalesYear(OrderDate);

-- data init for 5 M records.
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(5000000)
    OrderID = ROW_NUMBER() OVER (ORDER BY a.a)
    ,AutoIDRound = abs(checksum(newid()))
    ,Price = a.a * b.a * 10000
    ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.SalesOrder(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
SELECT 
    OrderID
    ,AutoID = cast(ROUND((13 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 101), 0) as int)
    ,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 10000), 0) as int)
    ,OrderQty
    ,Price = cast(Price AS DECIMAL(8,2))
    ,OrderDate = dateadd(day, -cast(ROUND((1099 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 1), 0) as int) ,`2017-01-10`)
FROM RoundData;
GO

假如目前使用者需要獲取所有汽車的製造商和相應的製造年份，即查詢語句如下：

SELECT Make, ModelYear FROM dbo.AutoType;

那麼，對於傳統的基於行儲存的結構，SQL Server會首先將AutoType這個表所有的資料頁（這裡假設佔用了3 頁）載入SQL Server記憶體快取中，然後篩選出兩個必須的列返回給使用者。換句話來說，在返回使用者必須的兩個欄位之前，使用者必須等待3個Pages的資料全部載入進入記憶體（這3個頁中包含了無用的其他三個欄位），如此勢必導致磁碟I/O，記憶體使用量和CPU開銷的浪費，最終必然導致使用者的執行時間會被拉長。
相反的，對於列儲存結構而言，列中資料是按列式儲存的（一個Column Segment只包含某一個列的資料），當使用者提交查詢以後，系統可以很快的拿到這兩個列的值，而無需去獲取其他三個多餘欄位的值。
以上文字描述，可以形象為如下的結構圖，圖的左上角為行儲存結構，圖的右上角為列儲存結構，圖的左下角是行儲存結構載入記憶體的過程，圖的右下角是使用者需要的最終結果。

基於以上的分析，我們清楚的知道了基於列儲存的結構和資料壓縮功能為SQL Server執行查詢語句大大節約了I/O消耗和因此而產生的CPU開銷。

查詢效能影響

基於上一節對列儲存索引的特殊儲存結構的分析，我們很清楚的知道列儲存索引在執行查詢過程中節約IOPS的同時，對資料倉儲類統計查詢語句效能有了非常大的效能提升，當然這裡面也避免不了有非常多的坑。這一節讓我們來看看列儲存索引相對於行儲存索引效能到底有多大的提升，在提升效能的同時，我們又需要踩過哪些坑，邁過哪些坎。

建立列儲存索引

為了便於接下來的效能對比，我們同時建立了傳統的B-Tree索引和列儲存索引，程式碼如下：

-- create regular B-Tree Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_OrderID 
ON dbo.SalesOrder(OrderID, OrderDate) 
WITH (ONLINE = ON)
ON ps_SalesYear(OrderDate);

--create nonclustered columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_ALL_Columns 
ON dbo.SalesOrder(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
GO

效能提升

首先讓我們來分析下列儲存索引為什麼會提升查詢效能：

列儲存索引可以提供非常高壓縮比，官方資料是可以節約10X的儲存開銷和成本。因為使用者表中每列儲存的資料屬性相同，資料內容相似，有的甚至有非常多的重複值。

高資料壓縮比為高效能查詢提供了基礎。因為在查詢過程中壓縮後的資料將使用更少磁碟IOPS來讀取，相同記憶體大小可以存放更多的資料，高效的磁碟I/O帶來CPU使用率的下降。

Batch Mode Processing提高查詢效能，官方資料可以提高2-4X的效能。因為SQL Server可以一次性處理多行資料。

使用者查詢經常使用某一列或者某幾個列欄位資料。列儲存索引可以大大減少物理磁碟的I/O開銷。

按照官方的資料統計列儲存索引查詢效能有10X到100X的效能提升，當然也取決於表結構的設計和具體的查詢語句。接下來讓我們用實際例子看看列儲存索引相較於B-Tree索引對查詢效能的提升。

/*--=====================================================
MS SQL Server 2012 columnstore index query performance compare
--=====================================================*/
USE ColumnStoreDB
GO

DBCC DROPCLEANBUFFERS
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON

-- Columnstore index query
SELECT 
    AutoID
    ,SalesQty = SUM(OrderQty)
    ,SalesAmount = SUM(UnitPrice)
FROM dbo.SalesOrder WITH(INDEX=NCSIX_ALL_Columns)
GROUP BY AutoID

-- B-Tree Index query
SELECT 
    AutoID
    ,SalesQty = SUM(OrderQty)
    ,SalesAmount = SUM(UnitPrice)
FROM dbo.SalesOrder WITH (INDEX = CIX_OrderID)
GROUP BY AutoID

I/O、CPU和執行時間的對比：

列儲存索引的I/O邏輯讀消耗為336，B-Tree索引I/O邏輯讀為21231，是前者的63倍；列儲存索引CPU消耗為125；B-Tree索引CPU消耗為2526，是前者的20倍；列儲存索引執行時間消耗為66，B-Tree索引執行時間消耗為873，是前者的13倍。

執行計劃中的效能消耗對比：

列儲存索引佔5%，而B-Tree 索引佔95%，也就是B-Tee儲索引效能消耗是列儲存索引的19倍。

執行計劃對比：

列儲存索引走的Batch Mode Processing，B-Tree索引走的是Row Mode Processing；列儲存索引的CPU預估消耗為0.275008，B-Tree索引的CPU預估消耗為2.75008，是前者的10倍；列儲存索引預估I/O消耗為0.0246065，B-Tee索引預估的I/O消耗為15.5661，是前者的632倍。

總結一下列儲存索引在IO讀、CPU消耗、時間消耗和執行計劃展示中的效能有13到63倍的效能提升，平均有28.75倍提升，總結如下圖所示：

踩坑場景

相較於B-Tree索引，列儲存索引在IO，CPU，時間消耗方面，列儲存索引的效率都有非常明顯的提升。但是，請不要高興得太早，如果不注意查詢語句的寫法，列儲存索引可能不會走高效的Batch Mode Processing，而走低效的Row Mode Processing，進而達不到你想要的效果。以下幾個小節是教大家如何升級打怪，踩坑邁坎。

INNER JOIN使用Batch Mode而OUTER JOIN使用Row Mode

在OLAP場景中，建立了列儲存索引的表，使用Batch Mode處理查詢效率要遠遠高於Row Mode處理。其中一種典型的情況是使用等值連線（INNER JOIN）SQL Server會採用Batch Mode來處理查詢，而使用外連結（OUTER JOIN）的情況，SQL Server會採取Row Mode處理查詢，效率天壤之別。讓我們來看看如何改寫OUTER JOIN查詢，使查詢計劃能夠走到Batch Mode，從而大大提高查詢效能。

INNER JOIN使用Batch Mode：顯示所有存在銷售記錄的汽車銷售收入和銷量

-- Batch mode processing will be used when INNER JOIN
SELECT 
    at.Model
    ,TotalAmount = SUM(ord.UnitPrice)
    ,TotalQty = SUM(ord.OrderQty)
FROM dbo.AutoType AS at
    INNER JOIN dbo.SalesOrder AS ord
    ON ord.AutoID = at.AutoID
GROUP BY at.Model

實際的執行計劃Actual Execution Mode為Batch

OUTER JOIN使用Row Mode：顯示所有汽車的銷售收入和銷量

-- BUT row mode processing will be used when OUTER JOIN
SELECT 
    at.Model
    ,TotalAmount = ISNULL(SUM(ord.UnitPrice), 0)
    ,TotalQty = ISNULL(SUM(ord.OrderQty), 0)
FROM dbo.AutoType AS at
    LEFT OUTER JOIN dbo.SalesOrder AS ord
    ON ord.AutoID = at.AutoID
GROUP BY at.Model
ORDER BY ISNULL(SUM(ord.UnitPrice), 0) DESC

實際的執行計劃Actual Execution Mode為Row

如何踩過OUTER JOIN使用Row Mode的坑呢？思路其實很簡單，因為從第一部分我們知道，使用INNER JOIN執行計劃是可以走到Batch Mode的，那麼我們可以先找出有銷售記錄的所有汽車的銷售額和銷量結果集(這個結果集應該已經非常小了)，然後再使用AutoType表來OUTER JOIN第一步的結果集，就得到我們想要的結果。

-- OUTER JOIN workaround
;WITH intermediateData
AS
(
    SELECT 
        at.AutoID
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
    FROM dbo.AutoType AS at
        INNER JOIN dbo.SalesOrder AS ord
        ON ord.AutoID = at.AutoID
    GROUP BY at.AutoID
)
SELECT 
    at.Model
    ,TotalAmount = ISNULL(itm.TotalAmount, 0)
    ,TotalQty = ISNULL(itm.TotalQty, 0)
FROM dbo.AutoType AS at
    LEFT OUTER JOIN intermediateData AS itm
    ON itm.AutoID = at.AutoID
ORDER BY itm.TotalAmount DESC

在處理掉資料量最大的這一步，實際的執行計劃Actual Execution Mode為Batch

OUTER JOIN踩坑前後寫法執行的CPU和時間消耗對比：

IN & EXISTS使用Row Mode

IN和EXISTS寫法在查詢列儲存表時，也是使用Row Mode，所以我們需要改寫為使用Batch Mode執行方式。方法是使用INNER JOIN方式或者是在IN字句裡面使用常量。

-- DEMO 2: IN & EXISTS both use row mode processing

IF OBJECT_ID(`dbo.HondaAutoTypes`, `U`) IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.HondaAutoTypes
    DROP TABLE dbo.HondaAutoTypes
END

SELECT *
    INTO dbo.HondaAutoTypes
FROM dbo.AutoType
WHERE make = `Honda`

-- IN use row mode
SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE ord.AutoID IN(SELECT AutoID FROM dbo.HondaAutoTypes)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- EXISTS use row mode too.
SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE EXISTS(SELECT TOP 1 * FROM dbo.HondaAutoTypes WHERE AutoID = ord.AutoID)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC


-- IN & EXISTS workaround using INNER JOIN
SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
    INNER JOIN dbo.HondaAutoTypes AS hat
    ON ord.AutoID = hat.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- or we also can use IN(<list of constants>) to make it use batch mode.
SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE ord.AutoID IN(104,106)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

執行計劃對比：

執行CPU和時間消耗對比：

UNION ALL

在大多數情況下UNION ALL操作會導致列儲存表走Row Mode執行方式，這種方式相對於Batch Mode執行效能低很多。改寫UNION ALL方式非常有技巧，具體的思路是：

先將UNION ALL的各個查詢分之彙總出來，然後將各分支的彙總資料再UNION ALL起來，最後再做一次統計彙總，就是我們想要的結果集了。

-- DEMO 3: UNION ALL usually use row mode

IF OBJECT_ID(`dbo.partSalesOrder`, `U`) IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.partSalesOrder
    DROP TABLE dbo.partSalesOrder
END

SELECT TOP 100 *
    INTO dbo.partSalesOrder
FROM dbo.SalesOrder
WHERE OrderID < 2500000;

-- UNION ALL mostly use row mode
;WITH unionSalesOrder
AS
(
    SELECT *
    FROM dbo.SalesOrder AS ord
    UNION ALL
    SELECT *
    FROM dbo.partSalesOrder AS pord

)

SELECT 
    OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
    ,TotalAmount = SUM(ord.UnitPrice)
    ,TotalQty = SUM(ord.OrderQty)
FROM dbo.AutoType AS at
    INNER JOIN unionSalesOrder AS ord
    ON ord.AutoID = at.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- UNION ALL workaround
;WITH unionSaleOrders
AS(
    SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
    FROM dbo.AutoType AS at
        INNER JOIN SalesOrder AS ord
        ON ord.AutoID = at.AutoID
    GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), unionPartSaleOrders
AS
(
    SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,TotalAmount = SUM(ord.UnitPrice)
        ,TotalQty = SUM(ord.OrderQty)
    FROM dbo.AutoType AS at
        INNER JOIN dbo.partSalesOrder AS ord
        ON ord.AutoID = at.AutoID
    GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), unionAllData
AS
(
    SELECT *
    FROM unionSaleOrders
    UNION ALL
    SELECT *
    FROM unionPartSaleOrders
)
SELECT 
    OrderDay
    ,TotalAmount = SUM(TotalAmount)
    ,TotalQty = SUM(TotalQty)
FROM unionAllData
GROUP BY OrderDay
ORDER BY OrderDay DESC

執行計劃的對比

執行過程CPU和時間消耗對比：

Scalar Aggregates

有時候，我們一個簡單的統計查詢記錄總數的操作在列儲存表中也可以有優化的寫法，思想是將一個一次性的統計彙總化簡為繁，使用分級彙總的方式獲取一個較小的結果集，最後再求總和。這種優化方法的思想是利用了第一次統計彙總可以使用Batch Mode操作方式，從而大大提高查詢效率。

-- DEMO 4: Scalar Aggregates
SELECT COUNT(*)
FROM dbo.SalesOrder

-- workaround 
;WITH salesOrderByAutoId([AutoID], cnt)
AS(
    SELECT [AutoID], count(*)
    FROM dbo.SalesOrder
    GROUP BY [AutoID]
)
SELECT SUM(cnt)
FROM salesOrderByAutoId

-- END DEMO 4

執行計劃對比：

執行CPU和時間消耗對比：

Multiple DISTINCT Aggregates

當SQL Server有兩個或者兩個以上的DISTINCT聚合操作的時候，會產生Table Spool操作，這個動作的產生當然會伴隨著Table Spool的讀寫操作，更要命的是SQL Server對Table Spool讀寫操作是單執行緒的並且採用Row Mode方式執行，所以執行效率非常低下。改寫的方式是採用將多個DISTINCT操作分開彙總，最後再INNER JOIN在一起，以此來避免Table Spool操作。

-- DEMO 5: Multiple DISTINCT Aggregates
SELECT 
        OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
        ,AutoIdCount = COUNT(DISTINCT ord.[AutoID])
        ,UserIdCount = COUNT(DISTINCT ord.[UserID])
FROM dbo.AutoType AS at
    INNER JOIN dbo.SalesOrder AS ord
    ON ord.AutoID = at.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)

-- workaround
;WITH autoIdsCount(orderDay, AutoIdCount)
AS(
    SELECT 
            OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
            ,AutoIdCount = COUNT(DISTINCT ord.[AutoID])
    FROM dbo.AutoType AS at
        INNER JOIN dbo.SalesOrder AS ord
        ON ord.AutoID = at.AutoID
    GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), userIdsCount(orderDay, UserIdCount)
AS(
    SELECT 
            OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
            ,UserIdCount = COUNT(DISTINCT ord.[UserID])
    FROM dbo.AutoType AS at
        INNER JOIN dbo.SalesOrder AS ord
        ON ord.AutoID = at.AutoID
    GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
)
SELECT
    auto.orderDay
    ,auto.AutoIdCount
    ,ur.UserIdCount
FROM autoIdsCount AS auto
    INNER JOIN userIdsCount AS ur
    ON auto.orderDay = ur.orderDay
-- END DEMO 5

執行計劃對比：

執行CPU和時間消耗對比：

限制條件

我們看到列儲存索引對統計查詢（資料分析OLAP或者資料倉儲Data Warehouse場景）效能有非常顯著的提升，按照微軟SQL Server的一向“惡習”，新特性的推出都附帶有很多的限制條件，當然列儲存索引也不例外。而這些限制條件在不同的SQL Server 版本中又各不相同，這一小節，讓我們來看看列儲存索引在SQL Server 2012版本中的諸多限制。

SQL Server 2012列儲存索引表的限制

每張表只允許建立一個列儲存索引

不支援建立Clustered列儲存索引，僅支援Nonclustered格式（注：從SQL Server 2014及以後的產品已經支援建立Clustered列儲存索引）

不支援建立Unique和Filtered列儲存索引

不支援對列儲存索引的排序ASC或者DESC功能

計算列不允許包含在列儲存索引中

列儲存索引不支援線上建立（ONLINE選項）

列儲存索引不支援Include字句

不允許重組列儲存索引

不支援在檢視上建立列儲存索引

建立了列儲存索引的表會成為只讀表，不允許UPDATE、DELETE和INSERT（注：從SQL Server 2014及以後開始已經提供可更新列儲存索引）

列儲存索引包含的列不能超過1024

列儲存索引不能包含稀疏列

列儲存索引不能包含Filestream列

列儲存索引不關心行記錄數和行資料庫分佈，所以不使用統計資訊

列儲存索引不能與以下功能共同使用

• 資料庫複製技術（Replication）
• 更改跟蹤（Change Tracking）
• 變更資料庫捕獲（Data Change Capture）
• 檔案流（Filestream）
• 行、列壓縮功能（Data Compression）

列儲存索引包含列欄位資料型別不允許：

• 二進位制資料型別：Binary、varbinary
• BLOB資料型別：ntext、text、image、varchar(max) 、nvarchar(max)、xml
• Uniqueidentifier
• Rowversion和timestamp
• sql_variant
• 精度大於18位的decimal和numeric
• 標量大於2的datetimeoffset
• CLR 型別

限制測試

以下是SQL Server 2012諸多限制的一個簡單測試。

/*--=====================================================
MS SQL Server 2012 Columnstore index limitations
--=====================================================*/

-- Just accept only one columnstore index
-- create another one
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not support CLUSTERED columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not support UNIQUE columnstore index
CREATE UNIQUE COLUMNSTORE INDEX UCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not accept ASC/DESC
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate ASC);
GO

-- Does not accept computed column
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate);
GO

-- Does not support online build index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate)
WITH (ONLINE = ON);
GO

-- Does not support include action
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate_@OrderID
ON dbo.SalesOrder(OrderDate)
INCLUDE(OrderID);
GO

-- Does not accept data length more than 18 numeric
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_UnitPrice
ON dbo.SalesOrder(UnitPrice);
GO

-- Doesn`t allow ALTER INDEX REORGANIZE
ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder REORGANIZE;

-- Doesn`t support create base on view
IF OBJECT_ID(`dbo.V_SalesOrder`, `V`) IS NOT NULL
    DROP TABLE dbo.V_SalesOrder
GO
CREATE VIEW dbo.V_SalesOrder
AS
SELECT TOP 100 *
FROM dbo.SalesOrder
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_V_SalesOrder_ALL_Columns 
ON dbo.V_SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- columnstore index table will be read only
UPDATE TOP (1) A
SET OrderQty = 100
FROM dbo.SalesOrder AS A

DELETE TOP(1) A
FROM dbo.SalesOrder AS A

INSERT INTO dbo.SalesOrder(OrderID, AutoID, OrderQty, Price, OrderDate)
SELECT TOP 1 OrderID = OrderID + 1, AutoID, OrderQty, Price, OrderDate
FROM dbo.SalesOrder

GO
-- There is no statistics for columnstore indexes
DBCC SHOW_STATISTICS(`dbo.SalesOrder`,`NCSIX_ALL_Columns`)

錯誤資訊如下：

Msg 35339, Level 16, State 1, Line 97
Multiple nonclustered columnstore indexes are not supported.
Msg 35338, Level 15, State 1, Line 103
Clustered columnstore index is not supported.
Msg 35301, Level 15, State 1, Line 107
CREATE INDEX statement failed because a columnstore index cannot be unique. Create the columnstore index without the UNIQUE keyword or create a unique index without the COLUMNSTORE keyword.
Msg 35302, Level 15, State 1, Line 112
CREATE INDEX statement failed because specifying sort order (ASC or DESC) is not allowed when creating a columnstore index. Create the columnstore index without specifying a sort order.
Msg 35339, Level 16, State 1, Line 117
Multiple nonclustered columnstore indexes are not supported.
Msg 35318, Level 15, State 1, Line 124
CREATE INDEX statement failed because the ONLINE option is not allowed when creating a columnstore index. Create the columnstore index without specifying the ONLINE option.
Msg 35311, Level 15, State 1, Line 128
CREATE INDEX statement failed because a columnstore index cannot have included columns.   Create the columnstore index on the desired columns without specifying any included columns.
Msg 35341, Level 16, State 1, Line 134
CREATE INDEX statement failed. A columnstore index cannot include a decimal or numeric data type with a precision greater than 18.  Reduce the precision of column `UnitPrice` to 18 or omit column `UnitPrice`.
Msg 35326, Level 16, State 1, Line 139
ALTER INDEX statement failed because a columnstore index cannot be reorganized. Reorganization of a columnstore index is not necessary.
Msg 2714, Level 16, State 3, Procedure V_SalesOrder, Line 1 [Batch Start Line 145]
There is already an object named `V_SalesOrder` in the database.
Msg 35305, Level 16, State 1, Line 152
CREATE INDEX statement failed because a columnstore index cannot be created on a view. Consider creating a columnstore index on the base table or creating an index without the COLUMNSTORE keyword on the view.
Msg 35330, Level 15, State 1, Line 157
UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.

這裡特別把列儲存索引不存在統計資訊截圖如下：

如何解決列儲存索引表只讀的問題

從限制條件章節，我們知道SQL Server 2012的列儲存索引表不允許進行DML操作，因為建立了列儲存索引的表會自動變成只讀表，那麼我們如何解決這個問題呢？

升級到SQL Server 2014或更高版本

將SQL Server 2012資料庫整個大版本升級到SQL Server 2014或者2016。因為從SQL Server 2014開始，列儲存索引表已經支援更新操作。當然升級動作非常複雜，只是為了解決列儲存表只讀問題而升級版本，有點本末倒置，因此這個方法不是本文的討論範疇。

禁用列儲存索引

在SQL Server 2012中解決列儲存索引表只讀問題的方法是，在執行DML語句之前，先禁用列儲存索引，完成DML以後，再重建列儲存索引。這個過程相當於先刪除列儲存索引，DML操作後重新建立列儲存索引。

-- DEMO: columnstore index table read_only fixing
USE ColumnStoreDB
GO

ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder DISABLE;
GO

UPDATE TOP (1) A
SET OrderQty = OrderQty + 1
FROM dbo.SalesOrder AS A

GO
ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder REBUILD;
GO

-- END DEMO

分割槽交換

分割槽交換方法的實現步驟如下：

新建一箇中間步驟表，表結構保持和列儲存表結構一致，包括欄位，資料型別，索引等

初始化一部分資料，注意OrderID不能與列儲存表OrderID有交集

做中間步驟表的SWITCH動作到列儲存表的一個空的分割槽上

-- DEMO: columnstore index table read_only fixing using partion switch
IF OBJECT_ID(`dbo.SalesOrder_staging`, `U`) IS NOT NULL
BEGIN
    DROP TABLE dbo.SalesOrder_staging
END
GO
CREATE TABLE dbo.SalesOrder_staging
(
    OrderID INT NOT NULL
    ,AutoID INT NOT NULL
    ,UserID INT NOT NULL
    ,OrderQty INT NOT NULL
    ,Price DECIMAL(8,2) NOT NULL
    ,UnitPrice AS Price * OrderQty
    ,OrderDate DATETIME NOT NULL,
    CONSTRAINT check_OrderDate CHECK ([OrderDate] > `2018-01-01 00:00` and [OrderDate]<=`2019-01-01 00:00`)
) ON [PRIMARY];

-- data init for 5 M records.
DECLARE
    @OrderID INT
;
SELECT @OrderID =  max(OrderID) 
FROM dbo.SalesOrder;

;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(500000)
    OrderID = @OrderID + ROW_NUMBER() OVER (ORDER BY a.a)
    ,AutoIDRound = abs(checksum(newid()))
    ,Price = a.a * b.a * 10000
    ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.SalesOrder_staging(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
SELECT 
    OrderID
    ,AutoID = cast(ROUND((13 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 101), 0) as int)
    ,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 10000), 0) as int)
    ,OrderQty
    ,Price = cast(Price AS DECIMAL(8,2))
    ,OrderDate = dateadd(day, cast(ROUND((330 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, `1` + replicate(`0`, len(AutoIDRound))) as bigint)) + 1), 0) as int) ,`2018-01-01`)
FROM RoundData;
GO

-- create regular B-Tree Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_OrderID
ON dbo.SalesOrder_staging(OrderID, OrderDate) 
WITH (ONLINE = ON)
ON [PRIMARY];
GO

--create nonclustered columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_ALL_Columns 
ON dbo.SalesOrder_staging(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
GO


ALTER PARTITION scheme ps_SalesYear NEXT used [PRIMARY];

--alter partition function pf_SalesYear() split range (`2019-01-01 00:00`);
--go

ALTER TABLE dbo.SalesOrder_staging switch TO dbo.SalesOrder 
PARTITION $PARTITION.pf_SalesYear(`2018-01-01 01:00`);
GO

注意：

SalesOrder_staging表的OrderID初始值比表SalesOrder表中的OrderID最大值要大

SalesOrder_staging表的Check約束必須要滿足Partition函式的限制，比如LEFT操作是左開右閉

SalesOrder_staging表的索引結構必須和SalesOrder表索引結構保持一致，否則Switch會報錯

如果SalesOrder表初始分割槽數不夠用，請使用ALTER PARTITION FUNCTION SPLIT方式分割出新的分割槽

由於Partition函式是LEFT，左開右閉，所以最後的Switch傳入日期不能是小於等於`2018-01-01 00:00`（我在這裡踩了坑，花了很長時間才走出來），必須比這個時間點稍微大些，否則系統會認為資料屬於前一個分割槽而導致Switch失敗。

最後總結

這篇文章從列儲存索引的幾個基本概念引入，談到了列儲存索引的結構以及列儲存索引對查詢效能的提升，然後談到了需要踩過的幾個坑，再接著聊到了列儲存索引在SQL Server 2012中的限制，最後講我們如何破解列儲存表只讀的問題。由於篇幅原因，關於SQL Server 2014和2016中的列儲存索引沒有過多涉及。預知後事如何，竊聽下月分解。

引用文章

Columnstore Indexes in SQL Server 2012

How to update a table with a columnstore index