【IT168 专稿】2012年，多款数据库陆续发布。从今天起，IT168将对今年发布的新款数据库进行评测，首先是微软数据库SQL Server 2012。
本文分为三个部分：
SQL Server 2012数据库安装
SQL Server 2012是美国微软公司最新开发的关系型数据库管理系统，于2012年3月7日发布。
支持SQL Server 2012的操作系统平台包括Windows桌面和服务器操作系统。软件版本包括免费的Express版和收费的Developer、Web、Standard、Enterprise和BI版，与上个版本SQL Server 2008 R2相比，SQL Server不再有Workgroup和Datacenter版本。值得注意的是，虽然开发版本支持Windows Vista,Windows 7等桌面操作系统，但Web、Enterprise和BI版本支持的操作系统版本只有2种Windows Server： Windows Server 2008和Windows Server 2008 R2。其中32位软件可以安装在32位和64位Windows Server上。由于Windows 8和Windows Server 2012发布时间晚于SQL Server 2012，其是否得到支持尚不明确。对于硬件平台，不再支持 SQL Server Itanium 版本。
几个版本的主要功能对比
SQL SERVER 2012 功能 | 企业版 | 商业智能版 | 标准版 |
支持最大内核数 | OS Max* | 16 Cores-数据库 | 16 Cores |
OS Max-商业智能功能 | |||
基本的 OLTP功能 | √ | √ | √ |
可编程性 (T-SQL, Data Types, FileTable) | √ | √ | √ |
可管理性 (SQL Server Management Studio, 基于策略的管理) | √ | √ | √ |
Basic Corporate BI (Reporting, Analytics, Multidimensional Semantic Model, Data Mining) | √ | √ | √ |
企业级商业智能 (报表, 分析, 多维商业智能语义模型) | √ | √ | √ |
自服务商业智能 (Alerting, Power View, PowerPivot for SharePoint Server) | √ | √ | |
企业数据管理(数据质量服务与主数据服务) | √ | √ | |
In-Memory Tabular BI Semantic Model | √ | √ | |
高级安全功能(高级审计，透明数据加密) | √ | ||
数据仓库 (列存储，压缩，) | √ | ||
高可用性 (AlwaysOn) | Advanced | Basic** | Basic** |
一、数据库安装
微软公司在其官方网站上提供了各种版本SQL Server软件，我们选择最新的SQL Server 2012简体中文评估版，其下载页面为：http://www.microsoft.com/zh-cn/download/details.aspx?id=29066,这个试用版有180天的试用期限制。试用版默认是企业版，是较高级的版本，比其他版本具有更多的功能，比如：高级安全功能和数据仓库等，也具有更大的扩展性，能利用更多的内存和CPU。几种版本的主要技术指标如下，更详细的表格，请访问http://msdn.microsoft.com/zh-cn/library/cc645993(v=SQL.110).aspx
功能名称 | Enterprise | 商业智能 | Standard | Web | Express |
单个实例使用的最大计算能力（SQL Server 数据库引擎）1 | 操作系统支持的最大值 | 限制为 4 个插槽或 16 核，取二者中的较小值 | 限制为 4 个插槽或 16 核，取二者中的较小值 | 限制为 4 个插槽或 16 核，取二者中的较小值 | 限制为 1 个插槽或 4 核，取二者中的较小值 |
单个实例使用的最大计算能力（Analysis Services、Reporting Services）1 | 操作系统支持的最大值 | 操作系统支持的最大值 | 限制为 4 个插槽或 16 核，取二者中的较小值 | 限制为 4 个插槽或 16 核，取二者中的较小值 | 限制为 1 个插槽或 4 核，取二者中的较小值 |
利用的最大内存（SQL Server 数据库引擎） | 操作系统支持的最大值 | 64 GB | 64 GB | 64 GB | 1 GB |
利用的最大内存 (Analysis Services) | 操作系统支持的最大值 | 操作系统支持的最大值 | 64 GB | 不适用 | 不适用 |
利用的最大内存 (Reporting Services) | 操作系统支持的最大值 | 操作系统支持的最大值 | 64 GB | 64 GB | 4 GB |
最大关系数据库大小 | 524 PB | 524 PB | 524 PB | 524 PB | 10 GB |
微软的产品安装中提供了本地化的联机帮助文档，同时在msdn网站提供在线浏览。在上面可以查到SQL Server各个版本的文档资料。
本次测试基于的运行环境是Intel Xeon E31270 3.4Ghz，内存4GB的PC服务器，物理CPU个数是1个，1TB SAS本地磁盘，采用一块 512M缓存 RAID卡，按RAID5方式组成磁盘阵列。操作系统采用Windows Server 2008 R2 简体中文标准版，这是一个只有x64平台的版本，将来微软的Windows服务器版均只支持x64。为了充分发挥操作系统和硬件的能力，SQL Server 2012选用的安装文件也是x64版本。
1.软件和数据库安装
1.1安装前准备工作
将下载的安装文件上传到待安装的Windows机器，如果是一个大的EXE文件，那么双击运行它，按照提示将实际安装文件解压缩到某个目录，在此目录下找到一个名为Setup.exe的文件，双击运行它即可进入安装界面。如果拥有DVD光盘，运行光盘根目录上的Setup.exe。如果下载的是一个包含多个平台的安装DVD镜像，则可以用虚拟光驱加载它，然后在虚拟光驱盘符根目录下找到Setup.exe。注意，在测试过程中采用这种方式安装报错，原因不明，建议有条件的用户还是采取第一种方式。
按照安装文档要求，SQL Server 2012在Windows Server 2008 R2上安装需要先安装操作系统SP1补丁，若没有安装，安装程序提示如下信息并中止安装。
1.2安装SQL Server 2012数据库软件
SQL Server评估版的安装过程比较直观，安装程序是图形界面，首先出来SQL Server安装中心的画面，点击左侧导航的“安装”文字，进入安装类型选择，如图1、图2所示。
▲图1
▲图2
点击第一项“全新SQL Server独立安装或向现有安装添加功能”。首先检查安装程序支持规则。如图3所示。
▲图3
第一步检查通过以后，点击确定，安装程序提示指定安装版本，默认是评估版，如图3。如果用户购买了正式的版本，则在第2个输入框输入产品序列号，安装程序根据序列号判断用户可安装的版本。
▲图4
接受许可协议后，安装程序开始安装程序支持文件，如果从虚拟光驱安装，这一步容易出现错误，如图5所示。
▲图5
如果出现类似图5的错误，可以通过把安装文件复制到硬盘，继而从硬盘安装解决此问题。
如果没有出错，下面安装就比较顺利了，基本上都是确定，下一步等等，需要用户选择的有下面几处：
选择安装角色。
▲图6
选择安装的组件和安装路径，这里点击全选按钮选择全部组件，并更改安装目录到硬盘空闲空间较多的逻辑盘下。如果系统盘有足够空闲空间，也可以使用默认值。
▲图7
在安装规则一步没有需要用户输入的信息。但是安装程序检测到了有些必需的系统组件没有启用。在这里是.NET Framework 3.5 Service Pack 1。
▲图8
点击“确定”按钮退出安装程序，并按照提示信息的要求，启用相应的组件，步骤如下：
在资源管理器上“我的电脑”图标上点击右键调出服务器管理器，在左侧导航栏选择“功能”条目。
▲图9
然后点击右侧页面“添加功能”。点击复选框选择“.NET Framework 3.5.1功能”，再点击“下一步”，系统随后会自动完成这项安装。
▲图10
▲图11
添加功能成功以后，需要重新运行安装程序，这次，安装规则一步顺利通过。
▲图12
在实例配置一步使用默认的实例名MSSQLSERVER,并根据需求更改实例根目录。如图13所示。
▲图13
在服务器配置一步，输入各种服务的用户名和口令，这里为了简单起见，所有服务采用默认的帐户名，密码留空。如图14所示。然后点击确定。
▲图14
在数据库引擎配置一步，指定操作系统和数据库混合认证，输入用户sa的口令，口令应包括字母和数字符号，以满足复杂性的要求，这个口令在以后的测试过程中会用到。点击“添加当前用户”按钮，指定数据库管理员。
▲图15
在分析服务配置一步，同样添加当前用户，如图16所示。
▲图16
在报表服务配置一步，选择默认配置，如图17所示。
▲图17
在分布式重播控制器配置一步，同样添加当前用户，如图18所示。
▲图18
在分布式重播客户端配置一步，选择默认配置，如图19所示。
▲图19
在准备安装一步，安装程序给出了当前的配置选项和配置文件，这个配置文件可用于将来的静默安装，如图20。静默安装的方法请参阅去年发表在IT168的《行式数据库评测-MS SQL Server》，在此不再重复。
▲图20
点击“安装”按钮，系统自动完成余下的安装步骤。请等待安装程序提示安装成功。如图21所示。
▲图21
1.3 创建和访问数据库
软件安装完成后，自动创建了系统数据库master,tempdb等，并启动了数据库服务，可以接受用户命令进行数据库的各项操作了。
运行Management Studio,使用Windows身份认证就可以连接到新安装的数据库。如图22所示。
▲图22
▲图23
如图23所示，系统数据库master等已经创建完成。
2.帮助文档的安装
SQL Server 2012与早先版本的SQL Server不同，安装介质中不包含产品文档，安装程序只是创建了联机帮助文档的查看器，而真正的产品文档却并没有安装，只能在联网的状态下查看，若要在在不联网的状态下查看，还得单独下载文档安装包。一般来说软件产品的文档应该默认安装，微软这么做的目的不明，但确实给用户带来了麻烦。
▲图24
2.1在线安装产品文档的步骤。
启动 SQL Server Management Studio，单击“帮助”菜单，然后选择“管理帮助设置”。启动帮助库管理器。
▲图25
在帮助库管理器中，单击“选择联机或本地帮助”。
▲图26
选择“我要使用本地帮助”，然后单击“确定”。
▲图27
回到上一个页面，点击“联机安装内容”，等候帮助管理器下载文档目录。
▲图28
如图28所示，文档目录既包括SQL Server 2012的文档，也包括其他开发文档，我们只要在所有需要安装的文档右侧点击“添加”。就可以点击“更新”下载并安装文档。
▲图29
根据网络速度和选择的文档的大小，安装文档需要一段时间，请等候文档安装完毕。
▲图30
现在再打开帮助查看器，可以发现左侧导航栏已经显示了我们选择安装的文档。
▲图31
2.2 从硬盘安装产品文档
为了满足无法连接因特网的用户安装文档的要求，微软给出用于受防火墙和代理限制的环境的 Microsoft SQL Server 2012 产品文档，其中中文版下载地址是：http://www.microsoft.com/zh-cn/download/details.aspx?id=347如图32。
▲图32
成功下载SQLServer2012Documentation_June_CHS.exe后，将文件复制到目标服务器硬盘，双击自解压缩 EXE 将内容解压缩到指定的位置，如:C:\SQLBOL。然后执行与在线安装相同的步骤。也是打开帮助管理器，选择“我要使用本地帮助”，然后单击“确定”。单击“从磁盘安装内容”，并浏览到您将下载的 EXE 解压缩到的目录。选择 HelpContentSetup.msha 文件，再单击“打开”。帮助介质的位置选定后，点击“下一个”。
▲图33
▲图34
安装失败，事件日志也没有提供足够的信息，看来这项功能还存在问题。期待微软解决这个问题。否则，无法连接因特网或位于防火墙和代理服务器后面的机器就无法访问文档。
▲图35
至此，安装过程结束。
${PageNumber}二、SQL Server 2012新功能简介
相比以往的版本，SQL Server 2012提供了许多新功能，详情见http://msdn.microsoft.com/zh-cn/library/bb500435，下面作一简要总结。
SQL Server 2012的新增功能或增强功能包括以下方面：
SQL Server 安装中的新增功能，数据库引擎、分析服务 (Analysis Services) 、集成服务 (Integration Services) 、数据质量服务(Data Quality Services)、主数据服务 (Master Data Services) 、报表服务 (Reporting Services) 和数据库复制的新增功能。
SQL Server 安装中的新增功能在第一节已经有所涉及，这里不再重复，分析服务等功能的测试涉及范围较广、这里也不再深入研究。只探讨数据库引擎的部分新增功能。
所有这些数据库引擎引入的新功能和增强功能可以提高设计、开发和维护数据存储系统的架构师、开发人员和管理员的能力和工作效率。
由于评测的条件和时间限制，这么多新功能无法一一测试，只能从应用程序使用的角度重点选择以下几种展开。
(1) 列存储索引
列存储索引简介
SQL Server 数据库引擎中的列存储索引可用于显著加快常见数据仓库查询的处理时间。 典型的数据仓库工作负荷涉及汇总大量数据。过去，在数据仓库和决策支持系统中通常用于提高性能的技术包括预先计算的汇总表、索引视图、OLAP 多维数据集等。尽管这些技术可较高提升查询处理的速度，但这些技术可能不灵活、难于维护并且必须针对每个查询问题进行专门设计。
通过使用 SQL Server 2012列存储索引，用户可以减少其他解决方案的开销。列存储索引还使查询能够快速计算结果，以致无需预先计算。列存储索引主要是为提高查询性能而设计的，作为一项新功能，它还有很多缺点，比如不支持表的DML操作，基本可以这么认为，它只适合于“只读”表，这点限制比某些完全的列存储数据库还要严格。
下面来实际体验一下列存储索引的创建及使用。
首先是创建列存储索引。
创建列存储索引语句语法如下：
与普通的创建索引语句相比，只有NONCLUSTERED和COLUMNSTORE关键字的不同，而普通索引分为两种，聚集和非聚集的，其实，正如前面技术特征中提到的，列存储索引只能是非聚集的，因此实质上，COLUMNSTORE隐含了NONCLUSTERED，不清楚为何微软要明确写出NONCLUSTERED关键字。
首先创建2个结构和内容完全一样的2个表。表emp用于对照测试普通索引，empcs用于测试列存储索引。
2> go
1> with t as( -- 用递归CTE（公用表表达式）生成300万行，注意必须写上列别名
2> select 1 empno,'EMP'+ CAST( 1 As Varchar(20))name,1%5 depno,1000*1%10 sal
3> union all
4> select empno+1,'EMP'+ CAST(empno+1 As Varchar(20)),empno%5+1,1000*(empno%10) from t where empno<3000000
5> )
6> insert into emp select * from t Option (Maxrecursion 0); -- Maxrecursion为0表示不限制递归次数
7> go
(3000000 行受影响)
1> select * into empcs from emp; -- 复制emp表到empcs表
2> go
(3000000 行受影响)
1> select getdate(); -- 由于set statistics time on命令未起作用，利用系统时间计时
2> select depno,count(*) from emp group by depno;
3> select getdate();
4> go
-----------------------
2012-07-01 19:28:16.640
(1 行受影响)
depno
----------- -----------
1 600000
2 600000
3 600000
4 600000
5 600000
(5 行受影响)
-----------------------
2012-07-01 19:28:16.927
(1 行受影响) --表扫描的时间为0.287秒
1> CREATE NONCLUSTERED INDEX IX_emp_dep ON emp(depno); --创建普通索引
2> go
1> set showplan_text on
2> go
1> select depno,count(*) from emp group by depno;
2> go
StmtText
-------------------------------------------------
select depno,count(*) from emp group by depno;
(1 行受影响)
StmtText
-----------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
|--Stream Aggregate(GROUP BY:([tpch].[dbo].[emp].[depno]) DEFINE:([globalagg1006]=SUM([partialagg1005])))
|--Parallelism(Gather Streams, ORDER BY:([tpch].[dbo].[emp].[depno] ASC))
|--Stream Aggregate(GROUP BY:([tpch].[dbo].[emp].[depno]) DEFINE:([partialagg1005]=Count(*)))
|--Index Scan(OBJECT:([tpch].[dbo].[emp].[IX_emp_dep]), ORDERED FORWARD)
(5 行受影响) --执行计划显示，用到了普通索引
1> set showplan_text off
2> go1> select getdate();
2> select depno,count(*) from emp group by depno;
3> select getdate();
4> go
-----------------------
2012-07-01 19:32:05.313
(1 行受影响)
depno
----------- -----------
1 600000
2 600000
3 600000
4 600000
5 600000
(5 行受影响)
-----------------------
2012-07-01 19:32:05.497
(1 行受影响) --普通索引扫描的时间为0.184秒
1> CREATE NONCLUSTERED COLUMNSTORE INDEX IX_empcs_dep ON empcs(depno); --创建列存储索引
2> go
1> set showplan_text on
2> go
1> select depno,count(*) from empcs group by depno;
2> go
StmtText
---------------------------------------------------
select depno,count(*) from empcs group by depno;
(1 行受影响)
StmtText
-----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
|--Stream Aggregate(GROUP BY:([tpch].[dbo].[empcs].[depno]) DEFINE:([globalagg1006]=SUM([partialagg1005])))
|--Sort(ORDER BY:([tpch].[dbo].[empcs].[depno] ASC))
|--Parallelism(Gather Streams)
|--Hash Match(Partial Aggregate, HASH:([tpch].[dbo].[empcs].[depno]), RESIDUAL:([tpch].[dbo].[emp
cs].[depno] = [tpch].[dbo].[empcs].[depno]) DEFINE:([partialagg1005]=COUNT(*)))
|--Index Scan(OBJECT:([tpch].[dbo].[empcs].[IX_empcs_dep]))
(6 行受影响) --执行计划显示，用到了列存储索引
1> set showplan_text off
2> go
1> select getdate();
2> select depno,count(*) from empcs group by depno;
3> select getdate();
4> go
-----------------------
2012-07-01 19:33:57.827
(1 行受影响)
depno
----------- -----------
1 600000
2 600000
3 600000
4 600000
5 600000
(5 行受影响)
-----------------------
2012-07-01 19:33:57.927
(1 行受影响) –列存储索引扫描的时间为0.1秒
接下来比较列存储索引和普通索引占用的空间，列存储索引占用的空间不足普通索引的一半，可见列存储索引的确解约了存储空间。
2> go
name rows reserved data index_size unused
--------- -------------------- ------------------ ------------------ ------------------ ------------------
empcs 3000000 141912 KB 117744 KB 24024 KB 144 KB
1> sp_spaceused 'EMP'
2> go
name rows reserved data index_size unused
--------- -------------------- ------------------ ------------------ ------------------ ------------------
emp 3000000 171896 KB 117744 KB 53744 KB 408 KB
如果查询的列不包含在索引中，此时若强制用提示让查询使用索引，结果比表扫描更慢。其中列存储索引比普通索引更慢一些。可见，列存储索引和基础表结合的访问效率不如普通索引。
2> select sal,count(*) from emp with(index(IX_emp_dep)) where depno=3 group by sal ;
3> select getdate();
4> go
-----------------------
2012-07-01 20:54:40.687
(1 行受影响)
sal
------------ -----------
2000.00 300000
7000.00 300000
(2 行受影响)
-----------------------
2012-07-01 20:54:41.113
(1 行受影响) --用普通索引的查询用时0.436秒
1> select getdate();
2> select sal,count(*) from emp where depno=3 group by sal ;
3> select getdate();
4> go
-----------------------
2012-07-01 20:55:02.903
(1 行受影响)
sal
------------ -----------
2000.00 300000
7000.00 300000
(2 行受影响)
-----------------------
2012-07-01 20:55:03.073
(1 行受影响) --用表扫描的查询用时0.17秒
1> select getdate();
2> select sal,count(*) from empcs with(index(IX_empcs_dep)) where depno=3 group by sal ;
3> select getdate();
4> go
-----------------------
2012-07-01 20:55:19.773
(1 行受影响)
sal
------------ -----------
2000.00 300000
7000.00 300000
(2 行受影响)
-----------------------
2012-07-01 20:55:20.300
(1 行受影响) --用列存储索引的查询用时0.527秒
1> select getdate();
2> select sal,count(*) from empcs where depno=3 group by sal ;
3> select getdate();
4> go
-----------------------
2012-07-01 20:55:38.657
(1 行受影响)
sal
------------ -----------
2000.00 300000
7000.00 300000
(2 行受影响)
-----------------------
2012-07-01 20:55:38.830
(1 行受影响) --用表扫描的查询用时0.173秒
1>
通过以上的测试，我们了解到，若要充分发挥列存储索引的作用，应尽量让数据库自己选择，而不是强制使用索引。而一般情况下，查询语句查询列和条件中所有的列都在索引时，使用索引才是最高效的。在实际创建索引时，应把查询语句的特点作为重要的考虑条件。
综上，列存储索引的用途与其说是索引，还不如说是基础表部分列的另一个列存储副本。因为一般数据分析都是通过列存储索引访问查询中出现的所有基础表的列。
当不在需要某个列存储索引或者需要修改它的定义时，需要删除列存储索引，语句语法与普通索引相同，如下所示：
另外，当需要对列存储索引的基础表进行DML操作时，可以首先禁用该索引，等DML操作完成后再重建它。语句语法也与普通索引相同，如下所示：
ALTER INDEX 索引名 ON 表名 REBUILD
禁用索引可防止用户访问该索引，对于聚集索引，还可防止用户访问基础表数据。重新生成索引将会删除并重新创建索引。
${PageNumber}(2) 序列
过去版本的SQL Server只有标识列(IDENTITY)，而缺乏单独的序列，SQL Server 2012版本起，开始提供了序列，并定义了在序列上的可以进行的一些操作。
序列是一种用户定义的架构绑定对象，它根据创建该序列时采用的规范生成一组数值。 这组数值以定义的间隔按升序或降序生成，并且可根据要求循环(重复)。序列不与表相关联，这一点与标识列不同。
序列是通过使用 CREATE SEQUENCE 语句独立于表来创建的。 其选项使您可以控制增量、最大值和最小值、起始点、自动重新开始功能和缓存以便改进性能。
与在插入行时生成的标识列值不同，应用程序可以通过调用 NEXT VALUE FOR 函数在插入行之前获取下一序列号。 在调用 NEXT VALUE FOR 时分配该序列号。 此 NEXT VALUE FOR 函数可用作表定义中某个列的默认值。使用 sp_sequence_get_range 可一次获取某个范围的多个序列号。
序列可定义为任何整数数据类型。 如果未指定数据类型，则序列将默认为 bigint。
创建序列语法
完整的语法图如下，其中必须的只有第一行，即CREATE SEQUENCE序列名，但这样创建的序列是从最小的负数开始的，不太符合正常使用习惯，因此，一般提供start with子句设置序列开始号码。
[ AS [ 内置整型 | 用户定义整型 ] ]
[ START WITH <常量> ]
[ INCREMENT BY <常量> ]
[ { MINVALUE [ <常量> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <常量> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <常量> ] } | { NO CACHE } ]
[ ; ]
下面介绍序列的几种简单操作。
2> INCREMENT BY 1 ;
3> go
1> select next value for seq1;--取出序列的当前值，和表无关
2> go
--------------------
1
(1 行受影响)
1> select next value for seq1;
2> go
--------------------
2
(1 行受影响)
1> create table a(a int);
2> go
1> insert into a values(next value for seq1); --把序列的当前值插入一个表的列
2> go
(1 行受影响)
1> insert into a values(next value for seq1);
2> go
(1 行受影响)
1> select * from a;
2> go
a
-----------
3
4
(2 行受影响)
1> insert into a select next value for seq1 +a from a;--如果对多行调用序列值，其中每行都取出一个新值
2> go
(2 行受影响)
1> select * from a;
2> go
a
-----------
3
4
8 --新值=列原来值3+序列值5
10 --新值=列原来值4+序列值6
(4 行受影响)
1> ALTER SEQUENCE seq1 restart with 10 INCREMENT BY 2;
2> go
1> select next value for seq1;
2> select next value for seq1;
3> select next value for seq1;
4> select next value for seq1;
5> select next value for seq1;
6> go
--------------------
10 --序列从修改的起点10开始，每次递增2
--------------------
12
--------------------
14
--------------------
16
--------------------
18
(1 行受影响)
1> ALTER TABLE a
2> ADD
3> DEFAULT NEXT VALUE FOR seq1 --将序列作为表中某列的默认值
4> FOR a;
5> GO
1> alter table a add b int;
2> go
1> insert into a(b) values(100); --插入不包括默认值列的其他列数据
2> insert into a(b) values(200);
3> go
(1 行受影响)
(1 行受影响)
1> select * from a;
2> go
a b
----------- -----------
3 NULL
4 NULL
8 NULL
10 NULL
20 100 --序列自动作为默认值填充
22 200
(6 行受影响)
1> select 'Tom'name union select 'Alice' union select 'Jack';
2> go
name
-----
Alice
Jack
Tom
(3 行受影响)
1> select name,next value for seq1 over (order by name) rn from(select 'Tom'name union select 'Alice' union select 'Jac
k') as e;--给表中数据按名字字母排序，顺序赋予一个序列号
2> go
name rn
----- --------------------
Alice 24
Jack 26
Tom 28
(3 行受影响)
(3) IIF和CHOOSE函数
SQL Server 2012新增了2个逻辑函数，分别是IIF和CHOOSE函数，它们是从Visual Basic借鉴过来的，对于简化某些情况下的语句很有帮助。
IIF函数有3个参数，它的语法如下：
函数返回类型是从 第二 和 第三个参数 的类型中返回优先级最高的数据类型。
IIF 是一种用于编写 CASE 语句的快速方法。它将传递的布尔表达式计算为第一个参数，然后根据计算结果返回其他两个参数之一。 也即，如果布尔表达式为 true，则返回第二个参数;如果布尔表达式为 false 或未知，则返回第三个参数。 第二 和 第三个参数 可以是任何类型。 适用于布尔表达式、null 处理和返回类型的 CASE 语句的相同规则也适用于 IIF。
IIF 转换为 CASE 这一事实也影响此函数的行为的其他方面。因为 CASE 语句最多可嵌套 10 层，所以，IIF 最多也只能嵌套 10 层。
示例：
2> go
Result
------
True
(1 行受影响)
1> SELECT IIF ( 45 > 30, NULL, NULL ) AS Result; --第二和第三个参数为NULL
2> go
消息 8133，级别 16，状态 1，服务器 WIN-BR1K22K3MUC，第 1 行
1> SELECT IIF ( NULL, 'True', 'False' ) AS Result; --第一个参数为NULL
2> go
消息 4145，级别 15，状态 1，服务器 WIN-BR1K22K3MUC，第 1 行
可见，若用错误的参数数据类型调用，此语句的结果也是一个错误。
CHOOSE函数根据索引号返回值，有不定个数的参数，它的语法如下：
第一个参数索引号是一个整数表达式，表示其后的项列表的从 1 开始的索引。
如果提供的索引值具有 int 之外的数值数据类型，则该值将隐式转换为整数。如果索引值超出了值数组的界限，则 CHOOSE 返回 Null。
后边的参数值1 … 值n 是任何数据类型的值列表，分别对应索引号为1、2、3…的项目。
函数返回类型 是从传递到函数的类型集中返回优先级最高的数据类型。
CHOOSE 返回值数组由跟在索引参数之后的各参数组成。 索引参数确定将返回以下哪些值。如果索引超出范围，返回NULL。
从下面的示例可见，CHOOSE函数从所提供的值列表中返回其中某项或者NULL(当索引为NULL或超出范围时)。
4)e;
2> go
x Result
----------- ------
1 Big
2 Middle
3 Small
4 NULL
(4 行受影响)
1> SELECT x,CHOOSE ( x, 'Big', 'Middle', 'Small' ) AS Result from(select cast(NULL as int)x union select 2 union select
3 union select 4)e;
2> go
x Result
----------- ------
NULL NULL
2 Middle
3 Small
4 NULL
(4 行受影响)
若没有CHOOSE函数，就需要写一个较长的case语句，而且每个索引值都需要列出。
${PageNumber}三、TPCH测试
这次主要测试查询性能，特别对列存储索引进行测试。产生数据源的软件是已经编译为可执行文件的Tcp-h_windows.zip，版本是2.1.8，可以从http://www.pilhokim.com/index.php?title=Project/EFIM/TPC-H下载。
1.准备工作
这里沿用SQL Server 2008 R2测评时使用的osql命令行工具它进行数据库创建、表创建、主键和外键创建、查询等工作，它有2种登录方式，用户名/口令方式和信任方式，后者在服务器所在计算机只要输入-E参数就可以登录。
它包括单行命令方式和交互方式。
构造测试环境，首先建立一个名为tpch的数据库，并设置它为当前数据库。然后执行创建表的脚本。注意在操作系统命令行中指定SQL脚本位置不需要用单引号括起，而在SQL命令中用到的文件路径需要用单引号括起。
首先用信任方式连接SQL Server数据库，输入创建数据库命令。
1> create database tpch
2> on (name=tpch,filename='F:\soft\1\tpch.mdf',size=20GB) --设定数据文件位置大小
3> log on(name=tpch_log,filename='F:\soft\1\tpch.ldf',size=5GB) --设置日志文件位置大小
4> go -- 约5分钟创建完成
1> alter database tpch set recovery simple; --改变恢复模式为简单模式
2> go
1> use tpch
2> go
创建测试表的脚本是位于压缩包的根目录的dss.ddl文件，可以直接使用，它的内容如下：
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
这个文件也可以在操作系统命令行执行，格式如下：
osql -E -d tpch -i dss.ddl -o F:\soft\1\crttab.log
2.测试数据生成和数据加载
测试数据文件用位于压缩包根目录的dbgen工具产生，用参数-s10产生10GB的数据文件，每个表对应一个数据文件。
这些文件的各自行数根据tpch的标准而定，数据分布是随机的。
SQL Server的大容量文本数据文件导入主要有2种方式，第一种在操作系统下，用bcp命令导入，这个命令的优点是支持各种文件格式，功能强大，缺点是比较复杂。第二种是在SQL Server交互界面中用Bulk insert语句导入，这个语句的语法简单，但支持的文件种类不如bcp多。两种方式都可以有效地减少日志的产生，达到高速导入的目的。
SQL Server 2008 R2测评时已经测试过这2种方式的性能，bcp方法略为胜出，由于没有文档表明SQL Server 2012对Bulk insert功能进行了改善，因此直接采用bcp方法。
为了加快导入，用-a参数把导入的array size改为40K，减少网络往返，同时用-b参数限制每批提交的数据行数为100万行，避免提交太频繁影响性能，而且使用-c参数默认文件中内容是文本，并指定列分隔符-t和行分隔符-r。连接方式使用信任方式参数为-T,注意与osql命令的-E区分。
导入8个表的命令行脚本及运行结果如下：
bcp customer in Tpc-h_windows\customer.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp lineitem in Tpc-h_windows\lineitem.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp part in Tpc-h_windows\part.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp partsupp in Tpc-h_windows\partsupp.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp region in Tpc-h_windows\region.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp nation in Tpc-h_windows\nation.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
bcp orders in Tpc-h_windows\orders.tbl -T -t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
--使用可信连接，并指定数据库为tpch
C:\Users\Administrator\Downloads>bcp supplier in Tpc-h_windows\supplier.tbl -T
-t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 100000 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 1014 平均值: (每秒 98619.33 行。)
C:\Users\Administrator\Downloads>bcp customer in Tpc-h_windows\customer.tbl -T
-t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已向 SQL Server 发送了 1000000 行。总共发送了: 1000000
已复制 1500000 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 19610 平均值: (每秒 76491.59 行。)
C:\Users\Administrator\Downloads>bcp lineitem in Tpc-h_windows\lineitem.tbl -T
-t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 59986052 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 889252 平均值: (每秒 67456.75 行。)
C:\Users\Administrator\Downloads>bcp part in Tpc-h_windows\part.tbl -T -t"|" -r
"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 2000000 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 26770 平均值: (每秒 74710.50 行。)
C:\Users\Administrator\Downloads>bcp partsupp in Tpc-h_windows\partsupp.tbl -T
-t"|" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 8000000 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 106252 平均值: (每秒 75292.70 行。)
C:\Users\Administrator\Downloads>bcp region in Tpc-h_windows\region.tbl -T -t"|
" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 5 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 16 平均值: (每秒 312.50 行。)
C:\Users\Administrator\Downloads>bcp nation in Tpc-h_windows\nation.tbl -T -t"|
" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 25 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 16 平均值: (每秒 1562.50 行。)
C:\Users\Administrator\Downloads>bcp orders in Tpc-h_windows\orders.tbl -T -t"|
" -r"|\n" -d tpch -c -a 40960 -b 1000000
开始复制...
已复制 15000000 行。
网络数据包大小(字节): 32576
总时钟时间(毫秒) : 183270 平均值: (每秒 81846.45 行。)
8个表bcp导入的时间分别是1秒、20秒、889秒、26秒、106秒、0秒、0秒、183秒，总时间1225秒，导入的总数据量约10GB,每秒约导入8MB。
3.查询语句的产生步骤
查询语句用位于压缩包queries 目录的qgen工具产生，需要针对SQL Server的特性作修改。
SQL Server对SQL语句的特殊要求主要有4点：
(1)取前若干行的语法。使用Top n写法。比如select top 1 * from t。
(2)日期间隔的表达式。不支持date，interval 'n' year/month等SQL 92写法，要改为 DateAdd函数。比如：date '1998-12-01' - interval '73' day 要改为dateadd(day,-73, '1998-12-01')。其中，用字符串'1998-12-01'直接代表日期。
(3)从日期提取年月日的表达式。不支持extract year from 等写法，要改为 DatePart函数或更简洁的Year函数。如：extract (year from o_orderdate )改为year(o_orderdate)
(4)用go命令执行SQL语句，可以在最后用1个GO命令批量执行一组分号结尾的SQL语句。这些SQL语句既可以是查询语句，也可以是DDL语句和DML语句。
将修改完成后的22个查询语句保存为mssql_tpch.sql文件。同时在文件开始处加入：
go
set staticstics time on
go
以限制查询的数据库，并显示SQL解析和运行时间。在文件末加上go命令。
在本次测试中，不知何故set statistics time on命令未起作用，所以利用系统时间计时，方法是在每个select语句的前后插入select getdate()语句。
查询语句的篇幅过长，不在正文列出，可以在本文的附件中下载。
${PageNumber}4.数据查询
SQL Server在创建主键后默认对主键列自动进行统计分析。其他列的统计分析可以用存储过程sp_createstats批量进行。原始tpch脚本dss.ri中创建外键的语法不符合SQL Server的格式要求，需要做相应的修改。为了便于便于在执行计划中辨认主键，对主键约束也采取命名方式。修改后的crt_pkfk.sql脚本如下：
go
set statistics time on
go
-- For table REGION
ALTER TABLE REGION ADD CONSTRAINT REGION_PK PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE NATION ADD CONSTRAINT NATION_PK PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) references REGION;
-- For table PART
ALTER TABLE PART ADD CONSTRAINT PART_PK PRIMARY KEY (P_PARTKEY);
-- For table SUPPLIER
ALTER TABLE SUPPLIER ADD CONSTRAINT SUPPLIER_PK PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY (S_NATIONKEY) references NATION;
-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD CONSTRAINT PARTSUPP_PK PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
-- For table CUSTOMER
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY (C_NATIONKEY) references NATION;
-- For table LINEITEM
ALTER TABLE LINEITEM ADD CONSTRAINT LINEITEM_PK PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
-- For table ORDERS
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_PK PRIMARY KEY (O_ORDERKEY);
-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD CONSTRAINT PARTSUPP_FK1 FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;
-- For table ORDERS
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
-- For table LINEITEM
ALTER TABLE LINEITEM ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references ORDERS;
ALTER TABLE LINEITEM ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;
go
set statistics time off
go
既可以直接在osql输入ALTER TABLE语句交互地创建主键和外键，也可以用如下命令行执行主键和外键的创建脚本。
根据SQL Server2008 R2的测试结果，聚集主键同时不采用压缩能提供最高的查询性能。这次测试只对这种方式进行，并将它和添加了列存储索引的结果进行比较。
利用前面第一步产生的查询脚本，执行测试的命令行如下,查询结果输出到不同的log文件，以便于比较：注意收集统计信息。
osql -E -d tpch -Q "sp_craetestats" -o stat.log
--创建聚集索引主键及外键
osql -E -d tpch -i crt_pkfk.sql -o crt_pkfk_comp.log
--查询
osql -E -d tpch -i tpch.sql -o tpch_t.log
由于计时命令不起作用，改用输出系统时间，然后用find命令来得到各次运行的时间，然后对相邻的2个时间作减法
C:\Downloads>find "2012-07-01" tpch_t.log
---------- TPCH_T_LOG.TXT
2012-07-01 10:23:18.250
2012-07-01 10:24:13.420 --第一个查询执行时间55.17秒
2012-07-01 10:24:21.313
2012-07-01 10:25:26.443
2012-07-01 10:26:30.133
2012-07-01 10:27:33.660
2012-07-01 10:28:26.687
2012-07-01 10:29:30.177
2012-07-01 10:30:34.030
2012-07-01 10:31:51.457
2012-07-01 10:32:56.080
2012-07-01 10:33:03.820
2012-07-01 10:34:07.093
2012-07-01 10:34:25.017
2012-07-01 10:35:18.633
2012-07-01 10:36:12.033
2012-07-01 10:36:21.617
2012-07-01 10:37:15.237
2012-07-01 10:38:11.750
2012-07-01 10:39:05.200
2012-07-01 10:40:05.933
2012-07-01 10:47:38.060
2012-07-01 10:47:49.443
第二节虽然对列存储索引进行了比较测试，但由于数据量较小，性能差距不十分明显，为了确定在TPCH查询中列存储索引是否有价值，首先为LINEITEM表创建一个列存储索引，观察添加索引前后，第一个查询语句的执行时间，从55秒缩短为4秒，速度提高了10倍多。因此下一步考虑对每个表添加列存储索引。
2> go
1> select getdate();
2> CREATE NONCLUSTERED COLUMNSTORE INDEX IX_LINEITEM ON LINEITEM --创建列存储索引包括查询中涉及的所有列
3>
4> (l_orderkey,L_PARTKEY,L_SUPPKEY,l_shipdate,l_returnflag,l_linestatus,l_quantity,l_extendedprice,l_discount,l_tax);
5> select getdate();
6> go
-----------------------
2012-07-01 21:59:44.960
(1 行受影响)
-----------------------
2012-07-01 22:02:23.760
(1 行受影响) --创建列存储索引用时2分38.8秒
1> sp_spaceused 'LINEITEM'
2> go
name
rows reserved data index_size unused
-----------------------------------------------------------------------------------------------------------------------
--------- -------------------- ------------------ ------------------ ------------------ ------------------
LINEITEM
59986052 9242184 KB 8280216 KB 961424 KB 544 KB
1> select getdate();
2> select
3> l_returnflag,
4> l_linestatus,
5> sum(l_quantity) as sum_qty,
6> sum(l_extendedprice) as sum_base_price,
7> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
8> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
9> avg(l_quantity) as avg_qty,
10> avg(l_extendedprice) as avg_price,
11> avg(l_discount) as avg_disc,
12> count(*) as count_order
13> from
14> lineitem
15> where
16> l_shipdate <= dateadd(day,-73, '1998-12-01')
17> group by
18> l_returnflag,
19> l_linestatus
20> order by
21> l_returnflag,
22> l_linestatus;
23> select getdate();
24> go
-----------------------
2012-07-01 22:09:47.423
(1 行受影响)
l_returnflag l_linestatus sum_qty sum_base_price
sum_disc_price sum_charge
avg_qty avg_price
avg_disc count_order
------------ ------------ ---------------------------------------- ----------------------------------------
---------------------------------------- ----------------------------------------
---------------------------------------- ----------------------------------------
---------------------------------------- -----------
A F 377518399.00 566065727797.25
537759104278.0656 559276670892.116819
25.500975 38237.151008
.050006 14804077
N F 9851614.00 14767438399.17
14028805792.2114 14590490998.366737
25.522448 38257.810660
.049973 385998
N O 750440706.00 1125264325266.58
1068995512488.7987 1111768538120.324852
25.498258 38233.907985
.050000 29431057
R F 377732830.00 566431054976.00
538110922664.7677 559634780885.086257
25.508384 38251.219273
.049996 14808183
(4 行受影响)
-----------------------
2012-07-01 22:09:51.390
(1 行受影响)
1>
(3 行受影响) --查询用时3.967秒
在第二节的测试中，我们了解到，对列存储索引的查询最好不产生回表，否则性能比普通索引的回表更差，因此，为满足查询要求，创建列存储索引基于下面的原则进行，如果是小表，则索引包含表中所有列，如果是大表且说明列不在查询语句中出现，则不包括该说明列，经过对22个查询语句中包含说明列的查找，只有SUPPLIER、CUSTOMER、ORDERS的说明列在查询语句中出现，因此其他的大表，不把说明列纳入列存储索引，以避免空间浪费。
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_NATION ON NATION( N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_REGION ON REGION( R_REGIONKEY,R_NAME,R_COMMENT);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_PART ON PART( P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE/*,P_COMMENT*/);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SUPPLIER ON SUPPLIER(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_PARTSUPP ON PARTSUPP(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST/*,PS_COMMENT*/);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CUSTOMER ON CUSTOMER(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ORDERS ON ORDERS(O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERDATE,O_TOTALPRICE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT);
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_LINEITEM ON LINEITEM(
L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE/*,L_COMMENT*/);
Go
--查询
osql -E -d tpch -i tpch.sql -o tpch_cs.log
C:\Downloads>find "2012-07-02 06:" tpch_cs.log
---------- TEST_CS4.TXT
2012-07-02 06:03:58.380
2012-07-02 06:04:01.560
2012-07-02 06:04:03.807
2012-07-02 06:04:07.800
2012-07-02 06:04:11.607
2012-07-02 06:04:14.230
2012-07-02 06:04:14.897
2012-07-02 06:04:17.270
2012-07-02 06:04:20.280
2012-07-02 06:05:52.070
2012-07-02 06:05:54.240
2012-07-02 06:05:54.633
2012-07-02 06:05:57.530
2012-07-02 06:06:35.940
2012-07-02 06:06:38.070
2012-07-02 06:06:39.417
2012-07-02 06:06:41.327
2012-07-02 06:06:43.463
2012-07-02 06:06:55.690
2012-07-02 06:06:56.490
2012-07-02 06:06:58.280
2012-07-02 06:07:13.083
2012-07-02 06:07:15.713
使用sp_spaceused命令查询创建列存储索引前后的数据和索引空间占用情况，可以发现，对于大表，列存储索引比数据占用的空间减少很多，即使是包含所有列的索引(红色数字)，也是如此。而不包含说明列的表，空间只有数据的十几分之一到四分之一，
表1 TPC-H scale=10数据和索引空间占用情况对比，计量单位:KB
表名 | 行数 | 数据 | 原索引 | 添加列存储索引后 | 列存储索引 | 列存储索引占数据比率 |
NATION | 25 | 8 | 8 | 40 | 32 | 400.0 |
REGION | 5 | 8 | 8 | 40 | 32 | 400.0 |
PART | 2000000 | 293656 | 640 | 73824 | 73184 | 24.9 |
SUPPLIER | 100000 | 15944 | 144 | 12504 | 12360 | 77.5 |
PARTSUPP | 8000000 | 1258112 | 2832 | 83152 | 80320 | 6.4 |
CUSTOMER | 1500000 | 261536 | 600 | 188536 | 187936 | 71.9 |
ORDERS | 15000000 | 1754416 | 3016 | 777816 | 774800 | 44.2 |
LINEITEM | 59986052 | 8280216 | 17608 | 1189144 | 1171536 | 14.1 |
合计 | 86586082 | 11863896 | 24856 | 2325056 | 2300200 | 19.4 |
下面是各组查询测试结果，取实际运行时间。
编号 | 聚集主键不压缩 | 列存储索引 | 列存储占原始时间百分比 |
01 | 00:55.2 | 00:03.2 | 5.76 |
02 | 00:07.9 | 00:02.2 | 28.47 |
03 | 01:05.1 | 00:04.0 | 6.13 |
04 | 01:03.7 | 00:03.8 | 5.98 |
05 | 01:03.5 | 00:02.6 | 4.13 |
06 | 00:53.0 | 00:00.7 | 1.26 |
07 | 01:03.5 | 00:02.4 | 3.74 |
08 | 01:03.9 | 00:03.0 | 4.71 |
09 | 01:17.4 | 01:31.8 | 118.55 |
10 | 01:04.6 | 00:02.2 | 3.36 |
11 | 00:07.7 | 00:00.4 | 5.08 |
12 | 01:03.3 | 00:02.9 | 4.58 |
13 | 00:17.9 | 00:38.4 | 214.29 |
14 | 00:53.6 | 00:02.1 | 3.97 |
15 | 00:53.4 | 00:01.3 | 2.52 |
16 | 00:09.6 | 00:01.9 | 19.93 |
17 | 00:53.6 | 00:02.1 | 3.98 |
18 | 00:56.5 | 00:12.2 | 21.64 |
19 | 00:53.4 | 00:00.8 | 1.50 |
20 | 01:00.7 | 00:01.8 | 2.95 |
21 | 07:32.1 | 00:14.8 | 3.27 |
22 | 00:11.4 | 00:02.6 | 23.10 |
合计 | 24:31.2 | 03:17.3 | 13.41 |
表2 TPC-H scale=10使用列存储索引前后的数据查询时间测试对比，单位：分:秒
从表2可知，如果使用聚集主键约束，SQL Server在大约25分钟运行完22个查询，大部分查询的运行时间均在1分钟左右，第21个查询速度最慢，用时7分32秒。而加上列存储索引后，在表不压缩的情况下，大约3分18秒钟就能执行完毕，大部分查询的运行时间均在2-3秒，不足原用时的十分之一，第2、16、18、22的速度提升比率略低，但也最多占原用时的29%，令人惊异的，第9和第13个查询，使用列存储索引的执行时间反而比不使用列存储索引更慢，增加的占用时间从18%～114%。可见，列存储索引虽然对部分查询有巨大的提升，但也有一些查询，目前的SQL Sever 2012还不能通过列存储索引提升其性能，而查询优化器并没有自动选择更有效率的原执行计划，看来还需要进一步改进。
四、小结
经过这次测试，我们对SQL Server 2012数据库已经有了初步的印象，安装比较复杂，功能很强大，总体性能也比较好。其次，对于TPC-H这种分析型查询，能产生较好的执行计划，能充分利用硬件资源，新增的列存储索引对查询性能提高较明显，对某些查询有数倍的提高。另外，在各方面都添加了新功能或增强功能，有利于编写应用程序的开发人员更好地利用数据库，也便于管理员提高数据库管理效率。
存在的问题，首先，安装过程第一步对先决条件的检查不完整，虽然指出操作系统平台补丁级别，但没有指出.Net Frame Work版本，容易造成安装中途失败，虽然安装人员有责任认真阅读安装指南，但产品也应该尽量人性化，避免浪费用户的时间。其次，SQL Server 2012修改了产品文档安装方式，由从介质安装改为从因特网下载，这不利于一些无法直接上网的计算机安装文档，另外，离线安装包安装失败提示信息不明确，不利于纠正错误。
还有一些功能上的问题。
当你添加了列存储索引，也就默认采用它。优化器即使在某些查询中用原始执行计划更优，也不能自动采用原始执行计划。
有些功能限制太多，如列存储索引不支持基础表DML操作。
set statistics time计时工具在特定情况下失效，没有找到解决办法。但Management Studio仍能显示计时。
总的来说，SQL Server 2012是一个功能全面，性能优良，运行稳定的主流商用数据库。本文没有展开介绍图形化的管理工具，而对最终用户来说，SQL Server的一大优势就是它的图形界面工具，这能显著降低入门难度，从而提高学习效率。建议大家多用图形界面，掌握这个软件产品。
