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.

Paging in SQL Server 2012

As we all know that MS has given us the FETCH NEXT clause in the query to apply paging. Let me explain this feature by adding a temp table of 1 million records.

with wcte as (

Select Row_number()over (order by a.name) as RNO

from sys.columns a, sys.columns b, sys.columns c

) Select rno , 'Name: ' + CAST(rno as varchar(10))  as Name

into tblTest

from wcte where RNO <= 1000000

Add primary key on RNO column through the designer. This will add a clustered index on this column.

Now, lets write the query for the SQL Server 2005 / 2008 / 2008 R2 using CTE;

;With wcte as (

Select Row_Number() over (Order by Rno) Rno2,* from tblTest WHERE Name is Not Null

) Select * from wcte where rno2 > 899979 and rno2 < 900000

order by rno

 If you look at the execution plan of this query, it will be like this;


Now lets write the query for SQL Server 2012 paging;

Declare @vPageNo int

Declare @vOffset int

Declare @vPageSize int

Set @vPageNo = 45000

Set @vPageSize = 20

Set @vOffset = (@vPageNo-1) * @vPageSize

SELECT *

FROM tblTest

WHERE Name is Not Null

ORDER BY rno

   OFFSET @vOffset ROWS

   FETCH NEXT @vPageSize ROWS ONLY

I have added the variables for passing OFFSET and PAGESIZE. This gives an additional hint for passing these from the front end as Input parameters of stored procedure.

The execution plan for this query is;

Now, it is very clear that the paging query of SQL Server 2012 performed very limited functions and performed 100% index scan. The SQL Server 2008 query performed many additional functions which makes it a bit smaller with my 2 column table.

Results might be different if you apply the same scenario directly on the clustered indexed column (RNO of tbltest in my case) instead of applying Row_Number() function. This is not the real time case as you never have IDs in sequence in you db tables as sequences break when you delete or migrate. More importantly, you apply paging on queries with WHERE clause. And you cannot use ID columns for paging in any case with WHERE Clause.