An Introduction to Sql 11 (Code Name Denali) –Part VII (Column Store Index in CTP 3)
Table of Content
- Introduction
- Background
- What is Row Store and Column Store
- Disadvantage of Row Store approach
- Advantages of Column Store over Row Store approach
- What is Column Store Index?
- Create column store index
- Manipulate data into a table having column store index
- Performance of Column store index
- Purpose of IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
- Present limitation of Column Store Index
- Ideal place to use Column Store Index
- Why will we use Column Store Index?
- Reference
- Conclusion
One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals. Part I describes about the features and enhancements in SSMS. Part II focuses on the new development and enhanced features from T-Sql perspective. Part III looks into the enhancements made from SSIS angle while Part 4 talks about Contained Database.
But that's not the end of Denali.It is an ongoing development and after the big success of CTP1 , it is now CTP 3 which was launched on 12th July 2011.As expected, Denali is spreading itself in various wings may it be enhancements from TSql perspective, a new look and feel-Juneau, a new report tool using the Project Cresent or enhancing the retrival of datawarehousing process by ColumnStore indexes etc. Denali has given a new dimension to the SQL Server database and this series will explore the new Column Store Index feature which is a part of project Apollo.
In the last few years, Microsoft has brought many technologies under the developers’ hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql 11(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements. In this article we will explore the new Column Store Index feature of Denali CTP 3. The subsequent articles will focus on the enhancements made in the other areas.
You can read my other articles on Denali as given under
- An Introduction to Sql 11 (Code Name Denali) –Part I (SSMS Features in CTP 1)
- An Introduction to Sql 11 (Code Name Denali) –Part II (T-Sql Features in CTP 1)
- An Introduction to Sql 11 (Code Name Denali) –Part III (SSIS Features in CTP 1)
- An Introduction to Sql 11 (Code Name Denali) -Part IV (Contained Database in CTP 1)
- An Introduction to Sql 11 (Code Name Denali) –Part V (SSIS Features in CTP 3)
- An Introduction to Sql 11 (Code Name Denali) –Part VI (T-Sql Features in CTP 3)
Before going to discuss what a Column Store Index is, it is worth spending some time in understanding what is Column Store and why it is in place.
We know that, Page is the fundamental unit of data storage in Sql Server. Data rows are the storage units of actual data. They start immediately after the Page header and are placed sequentially on the page. This way of storing records on the page in a row wise fashion is term as Row Store.
When values from a single column but from multiple rows are stored contiguously, it is call as Column Store.
If we refer to Figure 1, it is reveal that SQL Server always reads all the columns record in the query even when all of them are not needed.
As reveal from Figure 1, each page on disk stores values from only one column rather than entire rows.As a result, compression algorithms are much more effective because they can operate on homogeneous values. For example, say we have a table with 3 columns,the first being of type integer,second being varchar and the third being boolean field. Say we have 100 records or rows in that table. If we take the first column all the records is of type integer. So it is all homogenous and hence it will be easy to apply a compression algorithm in such a case and the compression will be huge. This also indicates that we do not need to access all the columns in the table and will only be interested in accessing the subset of columns which on the other hand reduces disk I/O and improves caching, thereby allowing the disk storage to be use more effectively such as index maintenance.
Column Store Index store columns in data pages as opposed to rows which was store in Row Store architecture. In ordinary index, rows are stored in disk pages but with column store index, columns are stored in separate set of the disk pages, and so it is faster.The query optimizer considers the columnstore index as a data source for accessing data just like it considers other indexes when creating a query plan.It is optimized for the improved and fast warehouse queries processing. Since in this case there is no need to read all the columns of a table, hence it provides significant savings in disk I/O and more data can fit into memory.
In Column Store approach, data is analyzed by columns.So,the lower the data cardinality i.e. the more repeating values a column has, the higher its compression rate will be.It uses the Vertipaq compression engine technology (it is also the compression engine in PowerPivot) to store columns than traditional indexes. In Row Store approach, all indexed data from each row is put together on a single page, and data in each column is spread across all pages in an index. In a column-store index, the data from each column is kept together so each data page contains data only from a single column as revealed from Figure 1.
The general syntax for creating a column store is as under
CREATE NONCLUSTERED COLUMNSTORE INDEX <ColumnStore_Index_Name> ON <Table_Name>(<Column1>,<Column2>,… <Column N>)
Where
ColumnStore_Index_Name => Name of the Column Store Index,
Table_Name => The Table object where the index will be created,
Column1 … Column N => The columns on which the indexes will be created.
The column store index can be created either by using the T-Sql Command or by using the Object Explorer in Management Studio
Suppose we have a table say tbl_ColumnStoreIndex with 4 columns say Column1 (type int and primary key), Column2 (type varchar(50)), Column3 (type DateTime), Column4 (type NUMERIC(16,2))
We will create column store index in this table for the last 3 columns.
TSql Aproach
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Column1 ON tbl_ColumnStoreIndex(Column1)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2_C3_c4
ON tbl_ColumnStoreIndex(Column2,Column3,Column4)
Now if we expand the table node in the object explorer and visit the Indexes folder of tbl_ColumnStoreIndex, we will find that the index has been created
Creating Column Store index thru Object Explorer
Step 1:First create the table either by using the table designer or by writing the tsql script
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Column1 ON tbl_ColumnStoreIndex(Column1)
Step 2:Expand the tree structure for the table and then right click on the Indexes icon -> New Index -> Nonclustered columnstore index.
The New Index window opens up
Let us give the Index Name and add columns by clicking on the Add button. After clicking on the Add button,the select column dialog box opens up from where we need to choose the columns
Once done, click on OK. At this stage we will get the below
Click Ok and the index will be created.
Data manipulation (any DML operation) into a table which already has a Column Store Index will take a little different approach.
If we first create the column store index and then try to populate the data as under
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Column1 ON tbl_ColumnStoreIndex(Column1)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2_C3_c4
ON tbl_ColumnStoreIndex(Column2,Column3,Column4)
--Insert record
Insert into tbl_ColumnStoreIndex Values('Col2 Val1',GETDATE(),10.23)
We will receive error as under
Msg 35330, Level 15, State 1, Line 16
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.
So the message says that, first we need to disable the columnstore index, then perform the insertion operation and then to rebuild the index again. So following the message let us try again
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Column1 ON tbl_ColumnStoreIndex(Column1)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2_C3_c4
ON tbl_ColumnStoreIndex(Column2,Column3,Column4)
-- Disabling the columnstore index before issuing the INSERT statement
ALTER INDEX IX_CS_C2_C3_c4 ON tbl_ColumnStoreIndex DISABLE;
--Insert record
INSERT INTO tbl_ColumnStoreIndex VALUES('Col2 Val1',GETDATE(),10.23)
INSERT INTO tbl_ColumnStoreIndex VALUES('Col2 Val2',GETDATE(),10.24)
-- Rebuilding the columnstore index after INSERT is complete
ALTER INDEX IX_CS_C2_C3_c4 ON tbl_ColumnStoreIndex REBUILD;
And this time it’s a success.
As an alternative we can first create the table , then add the record and then create the column store index as under
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
--Insert record
INSERT INTO tbl_ColumnStoreIndex VALUES('Col2 Val1',GETDATE(),10.23)
INSERT INTO tbl_ColumnStoreIndex VALUES('Col2 Val2',GETDATE(),10.24)
--Crate the indexes
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Column1 ON tbl_ColumnStoreIndex(Column1)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2_C3_c4
ON tbl_ColumnStoreIndex(Column2,Column3,Column4)
But this approach will work only for the first time. Next time onwards we have to insert or update or delete a column, we need to follow the first approach.
So if we issue the below statement,
UPDATE tbl_ColumnStoreIndex
SET Column2 = 'Updated Value'
WHERE Column1 = 1
We will receive the below error message
Msg 35330, Level 15, State 1, Line 3
UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.
For culminating the error we need to do the following
-- Disabling the columnstore index before issuing the INSERT statement
ALTER INDEX IX_CS_C2_C3_c4 ON tbl_ColumnStoreIndex DISABLE;
UPDATE tbl_ColumnStoreIndex
SET Column2 = 'Updated Value'
WHERE Column1 = 1
-- Rebuilding the columnstore index after INSERT is complete
ALTER INDEX IX_CS_C2_C3_c4 ON tbl_ColumnStoreIndex REBUILD;
SELECT * FROM tbl_ColumnStoreIndex
For measuring the performance of Column Store index, let us create a simple test environment. We will create two identical table with the difference that one table will have non column store index while the other will have so.
Script for table with Non column Store Index (N'tbl_Players_WithoutColumnStore)
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players_WithoutColumnStore' AND type = 'U')
DROP TABLE tbl_Players_WithoutColumnStore
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players_WithoutColumnStore (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15)
)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_PlayerID ON tbl_Players_WithoutColumnStore(PlayerID)
-- Creating normal nonclustered index
CREATE NONCLUSTERED INDEX IX_BelongsTo ON tbl_Players_WithoutColumnStore(BelongsTo)
--Prepare the data
;With Cte As
(
Select
Id=1
,PlayerName='Player' + CAST( 1 As Varchar(50))
,BelongsTo = Cast ('India' As Varchar(50))
Union All
Select
Id +1
,PlayerName= 'Player' + CAST( Id+1 As Varchar(50))
,BelongsTo = Cast(Choose(((ID%5)+1),'India','Pakistan','Srilanka','Bangaladesh','Bhutan')As Varchar(50))
From Cte
Where Id < 2000000
)
Insert into tbl_Players_WithoutColumnStore
Select PlayerName,BelongsTo from Cte
Option (Maxrecursion 0)
Table with Column Store Index(N'tbl_Players_WithColumnStore)
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players_WithColumnStore' AND type = 'U')
DROP TABLE tbl_Players_WithColumnStore
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players_WithColumnStore (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15)
)
--Prepare the data
;With Cte As
(
Select
Id=1
,PlayerName='Player' + CAST( 1 As Varchar(50))
,BelongsTo = Cast ('India' As Varchar(50))
Union All
Select
Id +1
,PlayerName= 'Player' + CAST( Id+1 As Varchar(50))
,BelongsTo = Cast(Choose(((ID%5)+1),'India','Pakistan','Srilanka','Bangaladesh','Bhutan')As Varchar(50))
From Cte
Where Id < 2000000
)
--Insert the record
Insert into tbl_Players_WithColumnStore
Select PlayerName,BelongsTo from Cte
Option (Maxrecursion 0)
-- Creating clustered index
CREATE CLUSTERED INDEX IX_PlayerID ON tbl_Players_WithColumnStore(PlayerID)
-- Creating column store nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_BelongsTo ON tbl_Players_WithoutColumnStore(BelongsTo)
And in each of the tables we have inserted 20,00,000(Twenty lacs) of data.
We can even get the information about the Column Store index created by querying the sys.column_store_index_stats catalog table as
SELECT
TableName = object_name(object_id)
, *
FROM sys.column_store_index_stats
So that now we have a huge collection of data, we can start the perfomance testing.
Let us run the below queries
Query 1: Without Column Store index
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
USE Test;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
BelongsTo
,Cnt = COUNT(*)
FROM tbl_Players_WithoutColumnStore WITH (INDEX (IX_BelongsTo))
GROUP BY BelongsTo
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
Query 2: With Column Store index
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
USE Test;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT
BelongsTo
,Cnt = COUNT(*)
FROM tbl_Players_WithColumnStore WITH (INDEX (IX_CS_BelongsTo))
GROUP BY BelongsTo
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
For the first query, the Server Executuion time is as under
CPU time = 640 ms, elapsed time = 2321 ms.
For the second query, the Server Executuion time is as under
CPU time = 0 ms, elapsed time = 44 ms.
We are interested mostly in the CPU time (time use to execute the query) and the Elapsed time (time taken by the query to run).A tablular comparison for the CPU and Executed Time is as under
| Index Type | CPU Time | Elapsed Time |
| Column Store | 0ms | 44 ms |
| Non Column store | 640ms | 2321 ms |
So from this we can infer that, for aggregate data Column store is a better choice than non column store index.
Also let us look into the execution mode for the first query(without column store)
As can be see that in the traditional approach, the storage mode is row store in which the data is processed one row at a time.
Now let us look into the execution mode for the second query(with column store)
The new batch execution mode is more efficient that the previous approach as it process data in batches as opposed to rows which is very essential for data warehousing processing where large volume of data with great workload resides.Each operator in an execution plan can use the row execution mode and when columnstore indexes are available, some operators can also use the batch mode. There is both an estimated and an actual execution mode and this information is displayed on the query execution plan.Also note that there is a new Physical operator i.e. the Column Store index scan.
If we want to disallow the use of Column Store index, we can use the IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX as under
SELECT
BelongsTo
,Cnt = COUNT(*)
FROM tbl_Players_WithColumnStore
GROUP BY BelongsTo
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);
From the execution plan we can make out that the physical operator has been changed to Clustered index scan.
In the present version, Column Store Index has some constraint which are as under
(A)Cannot create Clustered Column Store Index
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating COLUMNSTORE nonclustered index
CREATE CLUSTERED COLUMNSTORE INDEX IX_CS_C2_C3_c4
ON tbl_ColumnStoreIndex(Column2,Column3,Column4)
Msg 35335, Level 15, State 1, Line 11
CREATE INDEX statement failed because specifying key list is not allowed when creating a clustered columnstore index. Create the clustered columnstore index without specifying key list.
(B)Cannot create Nonclustered COLUMNSTORE Covering Index
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2
ON tbl_ColumnStoreIndex(Column2)
INCLUDE (Column1 ,Column3 ,Column4 )
Msg 35311, Level 15, State 1, Line 10
CREATE INDEX statement failed because a columnstore index cannot have included columns. Create the columnstore index on the desired columns without specifying any included columns.
(C)Cannot be created on a computed column
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 INT
,Column5 AS (Column1+Column4) -- It is a computed Column
)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2
ON tbl_ColumnStoreIndex(Column5)
CREATE INDEX statement failed because column 'Column5' on table 'tbl_ColumnStoreIndex' is a computed column and a columnstore index cannot be created on a computed column. Consider creating a nonclustered columnstore index on a subset of columns that does not include the column.
(D)Cannot create multiple Column Store Index
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_ColumnStoreIndex' AND type = 'U')
DROP TABLE tbl_ColumnStoreIndex
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_ColumnStoreIndex (
Column1 INT IDENTITY,
Column2 VARCHAR(50),
Column3 DATETIME,
Column4 NUMERIC(16,2)
)
-- Creating COLUMNSTORE nonclustered index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C2
ON tbl_ColumnStoreIndex(Column2)
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_C3
ON tbl_ColumnStoreIndex(Column3)
Msg 35339, Level 16, State 1, Line 14 Multiple nonclustered columnstore indexes are not supported.
We have seen that once we add a Column Store Index on a table, the table itself becomes read-only.If we need to insert new rows or update existing ones, we can disable the index, do data modifications and rebuild the column-store index. Because of this feature it is more suitable for data warehouse tables that contain static data
(A)The column data in the index is compressed, it takes less space.So Sql Server has to scan less number of data pages
(B)Since Sql Server has to perform less number of scan, so it will be able to keep them in memory buffer and the query performance will be faster
(C)Since less number of data pages need to be processed , the result will be faster and SQL Server will try to keep the columns cached in memory buffers
(D)Instead of accessing all rows, Sql Server has the opportunity to access selected columns
SQL Server Columnstore Index FAQ
In this article we have learnt about a new feature that project Apollo has offer us i.e. Column Store index.We have learnt the problem of the traditional approach and how column store will help in such situations, how to use it in DML statements. We have also done a performance benchmarking between the traditional and the new column store approach and have also discussed as why it is faster than the other approaches.
Hope this article will help us in understanding this new feature.
Thanks for reading the article.
