No.18

以往我們所了解的SQL DataBase ，在資料表及索引結構上大致有Clustered Index Structures、

Nonclustered Index Structures及Heap Structures，在本質上都是以資料行(Row)做為儲存依據

，而在新一代的SQL DataBase Denali提供了另一種方式，稱之為Columnstore Index，其本質上

是以資料欄(Column)做為儲存依據。

(取自：Columnstore Indexes for Fast DW QP SQL Server 11)

根據文件內容表示查詢效能提升455倍以上

這裡我直接建立一張Table來測試看看

(1)首先建立colstoreindex作為測試資料表，有三個Column，其內容有1670000筆資料，索引鍵如下

(2)接著直接以目前的狀態進行查詢，運用了Code這個index，其花費成本如下

(3)接著針對Production.WorkOrder Table建立Columnstore Index

create nonclustered columnstore index csindex_code
on colstoreindex(code,amount,initdate)

(4)同樣進行簡單查詢，發現雖然花費成本有降一些，但不明顯 2.73 : 2.14，差距不大

(5)接著測試彙總查詢，差距就很明顯了

--指定使用columnstore index 
select sum(amount)
from colstoreindex  with(index(csindex_code))
where code='08'


--指定使用一般index
select sum(amount)
from colstoreindex  with(index(IX_colstoreindex))
where code='08'

由這個簡單的測試，可以發現當查詢句應用在有彙總的情境下，Columnstore Index可以提升不少效能

，而Columnstore Indexes for Fast DW QP SQL Server 11文件，其測試的情境也是在彙總的情境下

此外目前Columnstore Index仍有些限制

1. Column Type，部份欄位型態是不支援的

(以下取自MSDN：http://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx)

The following data types cannot be included in a columnstore index:

• binary and varbinary

• ntext , text, and image

• varchar(max) and nvarchar(max)

• uniqueidentifier

• rowversion (and timestamp)

• sql_variant

• decimal (and numeric) with precision greater than 18 digits

• datetimeoffset with scale greater than 2

• CLR types (hierarchyid and spatial types)

• xml

2. Columnstore Index僅能唯讀，換句說當資料要變動時，目前只能先卸除Columnstore Index，

等到資料異動完成，再重建Columnstore Index，不過在MSDN上有這麼一段文字

『Do not create a columnstore index as a mechanism to make a table read-only. The restriction

on updating tables having a columnstore index is not guaranteed in future releases. When

read-only behavior is required it should be enforced by creating a read-only filegroup and moving

the table to that filegroup.』，似乎在未來有可能可以支援insert、 update & delete

Ref:

Columnstore Indexes for Fast DW QP SQL Server 11.pdf

資料表與索引組織

Heap Structures

Clustered Index Structures

Nonclustered Index Structures