I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.
In SQL Server 2012, columnstore index creation is supported only for the following data types:
- char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
- decimal (and numeric)
- int, bigint, smallint, and tinyint
- float, real
- money and smallmoney
There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.
CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)
We can force an index hint to use the columnstore index in a query if required:
… FROM mytable WITH (INDEX (mycsindex)) …
We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:
SELECT DISTINCT (SalesTerritoryKey)
CATALOG VIEWS and DMVs
There are a number catalog views which will show different values for ColumnStore. I am listing a few here:
type : 6
type_desc: NONCLUSTERED COLUMNSTORE
key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.
page_compression_success_count – Has a value of 0 for columnstore index.
Three additional properties have been added to the showplan data for columnstore indexes:
- Storage – row or columnstore
- EstimatedExecutionMode : row or batch
- ActualExecutionMode : row or batch
SSMS Enhancements for Columnstore
I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.
The next UI changes is for creating a New Columnstore Index.
The third part is around adding columnstore columns as we create the index.
These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.
This entry was posted on Wednesday, June 5th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.