SQL Server 2012新特性_列存储索引(3)

本篇Blog将介绍列存储索引相关的性能、查询提示及使用限制等方面的知识。若要了解列存储及列存储索引的概念及特征，请点击这里；若要了解列存储索引的创建与使用，请点击这里。 

列存储的性能
要了解类存储索引的性能到底如何，不妨先来搭建一下测试的环境。下面我们创建基本两张完全一样的表，不同的是，一张表没有列存储索引，而另一张则有：

不带column Store Index (N'tbl_Engineers_WithoutColumnStore)的脚本：

-- Drop the table if it exists
IF OBJECT_ID('tbl_Engineers_WithoutColumnStore') IS NOT NULL 
    DROP TABLE tbl_Engineers_WithoutColumnStore
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Engineers_WithoutColumnStore (
	EngineerID INT IDENTITY,
	EngineerName VARCHAR(15),
	BelongsTo VARCHAR(15)
)

-- Creating clustered index
CREATE CLUSTERED INDEX IX_EngineerID ON tbl_Engineers_WithoutColumnStore(EngineerID)
-- Creating normal nonclustered index
CREATE NONCLUSTERED INDEX IX_BelongsTo ON tbl_Engineers_WithoutColumnStore(BelongsTo)

--Prepare the data
;With Cte As
(
	Select 
		Id=1
		,EngineerName='Engineer' + CAST( 1 As Varchar(50))
		,BelongsTo = Cast ('Shanghai' As Varchar(50))
	Union All
	Select 
		Id +1 
		,EngineerName= 'Engineer' + CAST( Id+1 As Varchar(50)) 
		,BelongsTo = Cast(Choose(((ID%5)+1),'Shanghai','Henan','Jiangsu','Chongqing','Zhejiang')As Varchar(50))
			
	From Cte 
	Where Id < 2000000
)
Insert into tbl_Engineers_WithoutColumnStore
Select EngineerName,BelongsTo from Cte
Option (Maxrecursion 0)

 

带column Store Index (N'tbl_Engineers_WithColumnStore)的脚本：

-- Drop the table if it exists
IF OBJECT_ID('tbl_Engineers_WithColumnStore') IS NOT NULL 
    DROP TABLE tbl_Engineers_WithColumnStore
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Engineers_WithColumnStore (
	EngineerID INT IDENTITY,
	EngineerName VARCHAR(15),
	BelongsTo VARCHAR(15)
)
--Prepare the data
;With Cte As
(
	Select 
		Id=1
		,EngineerName='Engineer' + CAST( 1 As Varchar(50))
		,BelongsTo = Cast ('Shanghai' As Varchar(50))
	Union All
	Select 
		Id +1 
		,EngineerName= 'Engineer' + CAST( Id+1 As Varchar(50)) 
		,BelongsTo = Cast(Choose(((ID%5)+1),'Shanghai','Henan','Jiangsu','Chongqing','Zhejiang')As Varchar(50))
			
	From Cte 
	Where Id < 2000000
)
--Insert the record
Insert into tbl_Engineers_WithColumnStore
Select EngineerName,BelongsTo from Cte
Option (Maxrecursion 0)

-- Creating clustered index
CREATE CLUSTERED INDEX IX_EngineerID ON tbl_Engineers_WithColumnStore(EngineerID)
-- Creating column store nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX  IX_CS_BelongsTo ON tbl_Engineers_WithColumnStore(BelongsTo)

对上面两张表，都塞入了200万笔记录，下面开始性能测试:
执行下面的查询

查询1：不带列存储索引

查询2：带列存储索引

通过上面的两个查询，可以看出带与不带列存储索引执行的性能差异，很显然，带的时候无论在逻辑读取、预读，还是SQL Server执行时间上都具有明显的性能提升。

下面看看实际的执行计划对比：

查询1：不带列存储索引

查询2：带列存储索引

通过上面的执行计划对比图，我们可以看到，传统索引的存储模式是行存储，执行模式是按行处理，即一次仅处理一行；而对于列存储，执行模式是批处理。对于两种模式的效率或资源消耗上，列存储拥有传统的行存储无法比拟的优势，不仅处理的更快，而且I/O也更少。

 

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX的用途

如果在查询中不想使用非聚集的 columnstore 索引，我们可以使用查询提示（IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX）来解决，如下图：

从上面的执行计划中，我们可以看到使用IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX后，物理运算已经切换到Clustered Index Scan.

列存储索引的使用限制

• 不支持聚集列存储索引
  
• 列存储索引不能有包含的列
  
• 不能对计算列创建列存储索引
  
• 不支持多个非聚集列存储索引