SQL Server2014新特性：列存储索引方面的改进

参考：msdn  cnblogs

简介

SQL Server2012中只有非聚集的列存储索引（只读），从SQL Server2014开始有聚集的列存储索引（可更新），但2014依然不可以更新非聚集列存储索引。SQL Server2016+ 中可以更新非聚集列存储索引。


列存储
“列存储”是在逻辑上组织为包含行和列的表、在物理上以按列数据格式存储的数据。


行存储
“行存储”是在逻辑上组织为包含行和列的表、在物理上以按行数据格式存储的数据。 这是存储关系表数据的传统方法。 在 SQL Server 中，行存储是指基础数据存储格式为堆、聚集索引或内存优化表的表。


行组
“行组”是同时压缩为列存储格式的一组行。 每个行组通常可包含的最大行数是 1,048,576 行。


增量存储
“增量存储”仅用于聚集列存储索引，它是一个聚集索引，可以不断地存储行，并在行数达到某个阈值后，将行移入列存储，从而提高列存储压缩率和性能。


在大容量加载期间，大多数行直接转到列存储，而不通过增量存储中转。 在大容量加载结束时，某些行的数量可能无法满足行组的最小大小要求（即 102,400 行）。 在发生这种情况时，将最后的这些行转到增量存储而非列存储中。 对于少于 102,400 行的较小的大容量加载，所有行都直接转到增量存储中。


非聚集列存储索引
“非聚集列存储索引”和聚集列存储索引在功能上相同。 不同之处在于，非聚集索引是在行存储表上创建的辅助索引，而聚集列存储索引是整个表的主存储。


为何要使用列存储索引？
列存储索引可提供极高的数据压缩级别（通常是传统方法的 10 倍），从而明显降低数据仓库存储成本。 此外，对于分析，它们提供的性能比 btree 索引高出一个量级。 它们是数据仓库和分析工作负载的首选数据存储格式。


如何在行存储索引与列存储索引之间做出选择？
行存储索引最适合用于查找数据、搜索特定值的查询，或者针对较小范围的值执行的查询。 可对事务工作负载使用行存储索引，因为这些工作负载往往需要进行表查找而不是表扫描。


对于扫描大量数据（尤其是大型表中）的分析查询，列存储索引可提高性能。 可对数据仓库和分析工作负载（尤其是对事实数据表）使用列存储索引，因为它们往往需要进行全表扫描而不是表查找。


是否可以在同一个表中组合行存储与列存储？
是。 从 SQL Server 2016开始，你可以在行存储表上创建可更新的非聚集列存储索引。 列存储索引将存储所选列的副本，因此你确实需要为此准备额外的空间，但是，数据的压缩率平均可提高 10 倍。 如果采取这种做法，你可以同时对列存储索引以及行存储索引上的事务运行分析。 当行存储表中的数据更改时，列存储将会更新，因此这两个索引适用于相同的数据。


从 SQL Server 2016开始，你可以对一个列存储索引使用一个或多个非聚集行存储索引。

限制：

1. 最多支持1024列在你的索引中；
2. 列存储索引不能被定义为唯一性索引；
3. 不能创建视图；
4. 不能包含稀疏列；
5. 不能使用ALTER INDEX来修改索引，只能drop然后重新创建；(SQL Server2016+可以)
6. 不能使用INCLUDE关键字。
7. 不能排序列；
8. 不能使用FILESTREAM属性。
9. 当然还有一些数据类型不能包含在列存储索引中（binary , varbinary , ntext , text, , image, varchar(max) , nvarchar(max), uniqueidentifier, rowversion , sql_variant,精度大于18 的decimal，CLR 和xml等）

创建列存储索引：

--SQL Server2014
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	id INT NOT NULL
	,n NVARCHAR(50)
)
GO
--如果有主键，则列聚集存储索引无法创建成功
ALTER TABLE t ADD CONSTRAINT PK_t PRIMARY KEY (id)
CREATE UNIQUE INDEX UQ_t_id ON t(id)
CREATE INDEX ix_t_id ON t(id)

CREATE NONCLUSTERED columnstore INDEX ix_column_t_1 ON t(id,n)
CREATE NONCLUSTERED columnstore INDEX ix_column_t_2 ON t(id,n)
/*
不支持多个非聚集列存储索引。
*/
--非聚集列存储索引，无法插入数据
--注：在 SQL Server2016+ 中是可以插入的
INSERT INTO t VALUES(1,2)
/*
由于不能在包含非聚集列存储索引的表中更新数据，INSERT 语句失败。请考虑在发出 INSERT 语句前禁用该列存储索引，然后在完成 INSERT 后重新生成该列存储索引。
*/
--列存储索引不需要包含任何列
CREATE CLUSTERED columnstore INDEX ix_column_t_2 ON t
/*
不能对 表“t”创建多个聚集索引。请考虑使用 "with (drop_existing = on)" 选项创建新聚集索引。
*/
ALTER TABLE t DROP CONSTRAINT PK_t
CREATE CLUSTERED columnstore INDEX ix_column_t_2 ON t
/*
CREATE INDEX 语句失败，因为无法对具有非聚集列存储索引的表创建聚集列存储索引。请考虑删除所有非聚集索引，然后重试。
*/
DROP INDEX ix_t_id ON t
DROP INDEX UQ_t_id ON t
DROP INDEX ix_column_t_1 ON t	--注：列非聚集索引也得删除，才能创建 聚集列存储索引
--
CREATE CLUSTERED columnstore INDEX ix_column_t_2 ON t

其实性能提升才是关键， 但由于限制太多， 累觉不爱（很多大表都带有 nvarchar(max) 类型字段，无法使用） 。