Columnstore Indexes

This is the new feature added in SQL Server 2012 to overcome query performance and index data compression issues. The data stored in the columnstore index is pretty much compressed and very optimized. In SQL Srever 2012, if you have a columnstore index, you cannot perform the DML operations on the table i.e. you cannot insert / update or delete any record. The purpose of this index is for Data warehousing. You have to devise the logic in order to populate the data, for example table partitioning. But this issue is resolved in SQL Server 2014. But there, you will have to create a CLUSTERED COLUMNSTORE INDEX.

You can add this index simply by right clicking the Index folder under the table on which you want to add the column store index;

On clicking the option, a screen will open asking to add the column(s) on which you want to create a column store index (FirstName, LastName).

Leave rest of the properties to default. Click OK button to add the index.

Now, question is how the data will be stored? We have two types of data storge in sql server, RowStore and Column Store. Assume you have a tabel as

RNO       NAME          FNAME
1              Atif                Sheikh
2              Asif                Ahmed
3             Imran              Khan

In RowStore index, the data will be saved as;

Atif,Sheikh
Asif,Ahmed
Imran,Khan

The above data will be saved as a row in the 8k page.

And this will go on until the 8k page is complete.

In case of Columnstore index, the data will be stored as a segment of its respective column. Each segment have only one column. This goes for all the columns in the index. A Column can span on multiple segments and a segment may have multiple data pages. As for above example the data will be stored as;

Col1               Col2

Atif                  Sheikh
Asif                 Ahmed
Imran              Khan

These columns are just like pointers in the 8k page of the index. One page can have multiple columns

Now if you will query with NAME or FNAME in your WHERE condition, the data search and retrieval will be very fast as each condition will have to check one single column. You can check this with Statistics and Execution plans on you tables.

Indexed Views InDepth

To create an index on a view in SQL Server 2005, the view definition must not contain:

• ANY, NOT ANY
• OPENROWSET, OPENQUERY, OPENDATASOURCE
• arithmetic on imprecise (float, real) values
• OPENXML
• COMPUTE, COMPUTE BY
• ORDER BY
• CONVERT producing an imprecise result
• OUTER join
• COUNT(*)
• reference to a base table with a disabled clustered index
• GROUP BY ALL
• reference to a table or function in a different database
• The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted
• computed columns.
• Derived table (subquery in FROM list) reference to another view
• DISTINCT
• ROWSET function
• EXISTS, NOT EXISTS
• self-join
• expressions on aggregate results (e.g. SUM(x)+SUM(x))
• STDEV, STDEVP, VAR, VARP, AVG
• full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
• Subquery
• imprecise constants (e.g. 2.34e5)
• SUM on nullable expressions
• inline or table-valued functions
• table hints (e.g. NOLOCK)
• MIN, MAX
• text, ntext, image, filestream, or XML columns
• non-deterministic expressions
• TOP
• non-unicode collations
• UNION

The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted computed columns.

GROUP BY Restrictions

If GROUP BY is present, the VIEW definition:

• Must contain COUNT_BIG(*).
• Must not contain HAVING, CUBE, ROLLUP, or GROUPING()

These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions

Index Requirements

The user executing the CREATE INDEX statement must be the view owner. If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause. The index must not be created with the IGNOREDUPKEY option enabled.

Index Views will not help in the following cases;

• OLTP systems that have many writes to the disk.
• Databases that have many updates.
• Queries that do not involve aggregations or joins.
• High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
• The overuse of indexed views can be like winding a watch with a wrench.
• Online index rebuilds are not supported for indexed views.