非聚集列存储索引

/*
ColumnStore Index貌似适用于DW和读写分离用。
*/

create table testcolumnstore
( id int identity(1,1),
[data] int null
)

begin tran
declare @index int
set @index=0
while(@index<1000000)
begin
insert into testcolumnstore([data]) values(Floor(rand(abs(checksum(newid())))*1000))
set @index=@index+1
end
commit

--创建非聚集索引
create nonclustered columnstore index test_columnstore on testcolumnstore([data])
--普通非聚集索引
create nonclustered index test_noncolumnstore on testcolumnstore([data])

insert into testcolumnstore ([data]) values(1234)
/* sql 2012 列存储索引的表，为只读*/
alter index test_columnstore on testcolumnstore disable

insert into testcolumnstore ([data]) values(1234)

alter index test_columnstore on testcolumnstore rebuild

set statistics io on

select sum([data]) from testcolumnstore with(index(test_columnstore))

select sum([data]) from testcolumnstore with(index(test_noncolumnstore))