SQL Server 2016 新功能搶先看 - 資料行存放區索引

SQL Server 版本

資料行存放區

索引功能

SQL Server 2012

SQL Server 2014

SQL Server 2016 CTP2

SQL Database V12 Premium 版本

單一執行序查詢使用批次模式（Batch execution for single-threaded queries）

-

-

是

是

快照和讀取認可快照隔離（Snapshot isolation and read-committed snapshot isolation）

-

-

是

是

AlwaysOn 支援資料行存放區索引（AlwaysOn supports columnstore indexes）

是

是

是

是

AlwaysOn 可讀取次要支援可更新的資料行存放區索引（AlwaysOn readable secondary supports updateable columnstore indexes）

-

-

是

-

於堆積或btree的可更新非叢集資料行存放區索引（Updateable nonclustered columnstore index on heap or btree）

-

-

是

-

可更新的叢集資料行存放區索引（Updateable clustered columnstore index）

-

是

是

是

於記憶體最佳化資料表的資料行存放區索引（Columnstore index on a memory-optimized table）

-

-

是

-

create table CCITab1

(

 col1 int,

 col2 varchar(10)

)

go

create clustered columnstore index idx_ci on CCITab1

go

use master

go

if exists (select * from sys.databases where name = 'vNextDB')

        alter database vNextDB set single_user with rollback immediate

        drop database vNextDB

go

create database vNextDB

go

use vNextDB

go

--在堆積中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab1')

        drop table CCITab1

go

create table CCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

--在叢集資料表中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab2')

        drop table CCITab2

go

create table CCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

--在堆積中建立非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab1')

        drop table NCCITab1

go

create table NCCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_ncci nonclustered columnstore(col1)

)

go

--在叢集資料表中建立非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab2')

        drop table NCCITab2

go

create table NCCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ncci nonclustered columnstore(col1)

)

go

--在堆積中建立非叢集索引和非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab3')

        drop table NCCITab3

go

create table NCCITab3

(

 col1 int,

 col2 varchar(10),

 index idx_nci nonclustered (col1),

 index idx_ncci nonclustered columnstore(col1)

)

go

--在叢集資料表中建立非叢集索引和非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab4')

        drop table NCCITab4

go

create table NCCITab4

(

 col1 int primary key,

 col2 varchar(10),

 index idx_nci nonclustered(col2),

 index idx_ncci nonclustered columnstore(col1)

)

go

--停用非叢集資料行存放區索引

alter index idx_ncci on NCCITab1 disable

go

--新增資料

insert into NCCITab1 values (1,'abc')

go

--重建非叢集資料行存放區索引

alter index idx_ncci on NCCITab1 rebuild

go

--在堆積中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab1')

        drop table CCITab1

go

create table CCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_cci clustered columnstore

)

go

create nonclustered index idx_nci on CCITab1(col2)

go

--在叢集資料表中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab2')

        drop table CCITab2

go

create table CCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

create nonclustered index idx_nci on CCITab2(col2)

go

--於叢集資料行存放區索引的資料表建立非叢集索引和內涵資料行索引

if exists (select * from sys.tables where name = 'CCITab4')

        drop table CCITab4

go

create table CCITab4

(

 col1 int,

 col2 varchar(10),

 col3 bit,

 col4 nvarchar(10),

 index idx_cci clustered columnstore,

 index idx_nci nonclustered(col2),

)

create nonclustered index idx_ncii on CCITab4(col4) include (col3)

go

use master

go

--建立資料庫

if exists(select * from sys.databases where name = 'imoltp')

        alter database imoltp set single_user with rollback immediate

        drop database imoltp

go

create database imoltp

go

--啟用In-Memory OLTP

alter database imoltp add filegroup imoltp_mod contains MEMORY_OPTIMIZED_DATA

alter database imoltp add file (name='imoltp_file', filename='c:\imoltp_mod') to filegroup imoltp_mod

go

-- 建立記憶體最佳化資料表

 use imoltp

 go

-- 持久保存（預設）

 create table dbo.ShoppingCart (

   ShoppingCartId int not null primary key nonclustered hash with (bucket_count=2000000),

   UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000),

   CreatedDate datetime2 not null,

   TotalPrice money,

   index idx_cci clustered columnstore

 )

 with (memory_optimized=on)

 go

--在叢集資料表中建立篩選條件的非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab5')

        drop table NCCITab5

go

create table NCCITab5

(

 col1 int primary key,

 col2 varchar(10)

)

go

create nonclustered columnstore index idx_ncci on NCCITab5(col2)

where col2 is not null

if exists (select * from sys.tables where name = 'tempTab')

        drop table tempTab

go

create table tempTab

(c1 varchar(10) primary key)

go

if exists (select * from sys.tables where name = 'CCITab3')

        drop table CCITab3

go

create table CCITab3

(

 col1 int,

 col2 varchar(10),

 index idx_cci clustered columnstore,

 constraint fk_CCITab3_tempTab foreign key (col2)

        references tempTab(c1)

 )

go

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetails

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetailsCI

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetailsNCI

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

項目 使用索引

叢集索引和非叢集索引(內含資料行)

叢集資料行存放區索引和非叢集索引(內含資料行)

叢集索引和非叢集資料行存放區索引

掃描計數

5

4

8

實體讀取

3

0

0

LOB邏輯讀取

0

133,924

170,662

LOB讀取前讀取

0

363,138

616,904

略過區段

0

0

6