Columnstore index is an enterprise feature that is intended to improve the query response on T-SQL queries against OLAP systems. This feature was introduced in SQL Server 2012 as an alternative to the traditional row-based indexing that has been dominant in SQL Server versions prior to SQL Server 2012. It must be noted however that the notion of indexing against columns of a given object is not novel to Microsoft instead has been largely pioneered by SAP’s Sybase IQ database technology.
列存儲索引是一項企業功能，旨在改善針對OLAP系統的T-SQL查詢的查詢響應。 SQL Server 2012中引入了此功能，以替代傳統的基于行的索引，該索引在SQL Server 2012之前SQL Server版本中一直占主導地位。但是必須注意，針對給定對象的列進行索引的概念并非如此相反，對于Microsoft而言，這本新穎的書主要是由SAP的Sybase IQ數據庫技術開創的。
If you familiar with the creation of clustered or non-clustered indexes you should be able to find your way around creating a columnstore index. Figure 1 shows the complete syntax for creating a SQL Server 2012 columnstore index.
sqlserver索引的使用、 如果您熟悉集群索引或非集群索引的創建，則應該能夠找到創建列存儲索引的方法。 圖 1顯示了用于創建SQL Server 2012列存儲索引的完整語法。
Figure 1
圖1
Whilst the arguments of columnstore index creation are further explained in the TechNet site, it must be noted that the inclusion of the NONCLUSTERED argument does not make any difference during the creation of columnstore index in SQL Server 2012. The reason for this is that clustered columnstore indexes are not supported in SQL Server 2012 which means that every SQL Server 2012 columnstore index is always created as NONCLUSTERED columnstore index – regardless of whether or not you specify the NONCLUSTERED argument in your create columnstore index statement.
sql索引有什么用。 盡管TechNet網站中進一步解釋了列存儲索引創建的參數，但必須注意，在SQL Server 2012中創建列存儲索引的過程中，包含NONCLUSTERED參數沒有任何區別。其原因是群集的列存儲SQL Server 2012不支持索引，這意味著每個SQL Server 2012列存儲索引始終創建為NONCLUSTERED列存儲索引–不管您是否在create columnstore index語句中指定了NONCLUSTERED參數。
Thus, Figure 2 and Figure 3 show the respective creation of columnstore indexes against dbo.FactProductInventory_ApexSQL_CC1 and dbo.FactProductInventory tables.
因此， 圖2和圖3分別顯示了針對dbo.FactProductInventory_ApexSQL_CC1和dbo.FactProductInventory表的列存儲索引的創建。
Figure 2???????????????????????????????????????????????????????????????????????????????????????????????????????????????????Figure 3
索引存儲方式、 圖2 圖3
Although the script in Figure 2 differs from the one shown in Figure 3 through its omission of the NONCLUSTERED argument, the execution of both scripts invariably results into the creation of NONCLUSTERED index as shown in Figure 4.
盡管圖2中的腳本由于省略了NONCLUSTERED參數而與圖3所示的腳本有所不同，但是兩個腳本的執行總是導致圖4所示的NONCLUSTERED索引的創建。
Figure 4
避免在索引列上使用、 圖4
In addition to getting started with the syntax of creating columnstore index, you will notice that there is now a new icon in SQL Server 2012 object explorer that indicates the presence of a columnstore index in a given object as shown in Figure 5.
除了開始創建列存儲索引的語法外，您還將注意到SQL Server 2012對象瀏覽器中現在有了一個新圖標，該圖標指示給定對象中存在列存儲索引， 如圖5所示。
Figure S5
列式數據庫索引？ 圖S5
Finally, as you continue getting familiar with columnstore index, take note of the list of data types that are permitted in a create columnstore index statement. Table 1 outlines a list of supported data types as per SQL Server Data Type Category.
最后，隨著您繼續熟悉列存儲索引，請注意create columnstore index語句中允許的數據類型列表。 表1列出了每個SQL Server 數據類型類別所支持的數據類型。
Table 1
sql的索引， 表格1
| Character Strings | Unicode Character Strings | Exact Numerics | Approximate Numerics | Date and Time |
| CHAR | NCHAR | INT | FLOAT | DATE |
| VARCHAR – except for MAX values | NVARCHAR – except for MAX values | BIGINT | REAL | DATETIME |
| SMALLINT | DATETIME2 | |||
| TINYINT | SMALLDATETIME | |||
| BIT | TIME | |||
| MONEY | DATETIMEOFFSET – with a scale less than 2 | |||
| SMALLMONEY | ||||
| DECIMAL | ||||
| NUMERIC |
| 字符串 | Unicode字符串 | 精確數值 | 近似數值 | 日期和時間 |
| 焦炭 | NCHAR | INT | 浮動 | 日期 |
| VARCHAR – MAX值除外 | NVARCHAR – MAX值除外 | 比金特 | 真實 | 約會時間 |
| 小靈通 | DATETIME2 | |||
| 天音 | 約會時間 | |||
| 比特 | 時間 | |||
| 錢 | DATETIMEOFFSET –比例小于2 | |||
| 小錢 | ||||
| 十進制 | ||||
| 數字 |
Unfortunately, notwithstanding some of the potential gains in columnstore indexing, there are several restrictions. In this section we will take a look at the limitations of columnstore index usage in SQL Server 2012. However, due to the fact SQL Server 2014 had already been released at the time of writing this article, it must then be noted that some of the columnstore index restrictions applicable to SQL Server 2012 have since been resolved in SQL Server 2014 – i.e. creation of clustered columnstore index is now supported in SQL Server 2014. Nevertheless, as of SQL Server 2012, the limitations of columnstore index were as follows:
不幸的是，盡管列存儲索引有一些潛在的好處，但還是有一些限制。 在本節中，我們將研究SQL Server 2012中列存儲索引使用的限制。但是，由于在撰寫本文時已經發布了SQL Server 2014，因此必須注意此后，SQL Server 2014中已解決了適用于SQL Server 2012的列存儲索引限制-即，SQL Server 2014現在支持創建群集的列存儲索引 。 但是，從SQL Server 2012開始，列存儲索引的限制如下：
SQL Server 2012 Data Types that do not support Column-Store Indexes:
sql中的索引， 不支持列存儲索引SQL Server 2012數據類型：
Table 2
表2
| Character Strings | Unicode Character Strings | Exact Numerics | Binary Strings | Date and Time | Other Data Types |
| VARCHAR (MAX) | NVARCHAR (MAX) | DECIMAL – with precision greater than 18 digits | BINARY | DATETIMEOFFSET – with scale greater than 2 | XML |
| NUMERIC – with precision greater than 18 digits | VARBINARY | HIERARCHYID | |||
| IMAGE | SPATIALTYPES | ||||
| TIMESTAMP | |||||
| SQLVARIANT | |||||
| UNIQUEIDENTIFIER | |||||
| ROWVERSION |
| 字符串 | Unicode字符串 | 精確數值 | 二進制字符串 | 日期和時間 | 其他數據類型 |
| VARCHAR （最大值） | NVARCHAR （最大） | 十進制 -精度大于18位 | 二進制 | DATETIMEOFFSET-比例大于2 | XML格式 |
| NUMERIC –精度大于18位 | VARBINARY | 等級 | |||
| 圖片 | 空間類型 | ||||
| 時間戳 | |||||
| SQLVARIANT | |||||
| 唯一標識符 | |||||
| ROWVERSION |
In this section we take a closer look at some of the benefits that can be incurred by using a columnstore index in a data warehouse environment. We will be using the AdventureWorksDW2012.dbo.FactProductInventory table to test the regular index part of this tutorial. I have chosen this table as it has largest record count (at 776286) of all the objects that ships with the AdventureWorksDW2012 sample database.
此列列表已索引， 在本節中，我們將仔細研究在數據倉庫環境中使用列存儲索引可帶來的一些好處。 我們將使用AdventureWorksDW2012.dbo.FactProductInventory表測試本教程的常規索引部分。 我選擇此表是因為它在AdventureWorksDW2012示例數據庫附帶的所有對象中具有最大記錄數（為776286）。
Figure 6 shows the entity relationship diagram of the dbo.FactProductInventory table. The clustered index and primary key uses a combination of ProductKey and DateKey fields.
圖6顯示了dbo.FactProductInventory表的實體關系圖。 聚集索引和主鍵使用ProductKey和DateKey字段的組合。
For testing the columnstore index part, I have done the following:
為了測試columnstore索引部分，我做了以下工作：
Created a nonclustered columnstore index called CIX_FactProductInventory_ApexSQL on the dbo.FactProductInventory_ApexSQL table (Figure 7 shows the definition of the CIX_FactProductInventory_ApexSQL columnstore index).
在dbo.FactProductInventory_ApexSQL表上創建了一個名為CIX_FactProductInventory_ApexSQL的非聚集列存儲索引（ 圖7顯示了CIX_FactProductInventory_ApexSQL列存儲索引的定義）。
Figure 7
圖7
Now for the purposes of testing both scenarios (row based vs columnstore indexes), I am going to apply the same sample user story against dbo.FactProductInventory and dbo.FactProductInventory_ApexSQL. The user story is as follows:
現在，出于測試這兩種方案（基于行與列存儲的索引）的目的，我將針對dbo.FactProductInventory和dbo.FactProductInventory_ApexSQL應用相同的示例用戶故事 。 用戶故事如下：
“As a user, I would like to see a report that show the totals of Unit Costs, Units In, Units Out, and Units Balance per Product”
“作為用戶，我希望看到一個報告，其中顯示每個產品的單位成本，單位輸入，單位輸出和單位余額的總計”
Figure 8 and Figure 9 show respective T-SQL queries for retrieving the data to satisfy the user story against dbo.FactProductInventory and dbo.FactProductInventory_ApexSQL tables.
圖8和圖9分別顯示了T-SQL查詢，用于檢索數據以滿足dbo.FactProductInventory和dbo.FactProductInventory_ApexSQL表的用戶需求 。
Figure 8
圖8
Figure 9
圖9
Figure 10 and Figure 11 show IO Statistics for the respective execution of T-SQL queries shown in Figure 8 and Figure 9. Just by comparing the statistics in terms of logical reads and object scan count, it can be seen that there are significant performance benefits for the table that uses a columnstore index.
圖10和圖11顯示了分別執行圖8和圖9所示的T-SQL查詢的IO統計信息。 僅通過比較邏輯讀取和對象掃描計數方面的統計信息，就可以看出使用列存儲索引的表具有顯著的性能優勢。
Figure 10
圖10
Figure 11
圖11
Finally, as probably could have been expected after looking at the IO Statistics, the execution plan of the T-SQL query that uses the columnstore index (shown in Figure 13) is much simpler and less costly as compared to that of the T-SQL query that uses a regular row-based index (shown in Figure 12).
最后，就像在看完IO統計信息后可能會預期的那樣，與T-SQL相比，使用列存儲索引的T-SQL查詢的執行計劃（如圖13所示）要簡單得多且成本更低。使用常規基于行的索引的查詢（如圖12所示）。
The newly introduced concept of indexing columns in SQL Server 2012 known as columnstore index can significantly improve the response to T-SQL query requests. However, in spite of such improvements, there are several restrictions to columnstore index usage that may force one to relinquish SQL Server 2012 features such as replication and change tracking for the benefit of columnstore indexing.
SQL Server 2012中新引入的索引列概念（稱為列存儲索引）可以顯著改善對T-SQL查詢請求的響應。 但是，盡管進行了這樣的改進，但列存儲索引的使用仍存在一些限制，這些限制可能會迫使人們放棄SQL Server 2012功能（例如復制和更改跟蹤）以利于列存儲索引。
翻譯自: https://www.sqlshack.com/columnstore-index-sql-server/
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
