How is row storage index different from Column Storage Index?

First of all ColumnarStorage Index  is a new feature in SQL Server 2012 and upward versions. This new indexing type is designed to speed up disk I/O operations in queries retrieving data from data warehouses where it is typically used. In data warehousing with ColumnarStorage Index; data is stored in columnar fashion. Hence if the data consists of large number of columns but only few columns are regularly called upon in a query, then having those columns (indexed) arranged to store data in the sense of columnar storage will be more efficient.

Probably this Mickey Mouse picture shows these two different index types.

Microsoft puts it this way,

"An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index.
"
There are many restrictions on creating Columnar Storage Index, the first of which is that you can only do it if you have SQL Server 2012 Enterprise edition.

These are the basic restrictions for the columnstoreindex:

• Cannot have more than 1024 columns.
• Cannot be clustered. Only nonclustered columnstore indexes are available.
• Cannot be a unique index.
• Cannot be created on a view or indexed view.
• Cannot include a sparse column.
• Cannot act as a primary key or a foreign key
• Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead. (You can use ALTER INDEX to disable and rebuild a columnstore index.)
• Cannot be created with the INCLUDE keyword.
• Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting is not allowed in the index. Values selected from a columnstore index might be sorted by the search algorithm, but you must use the ORDER BY clause to guarantee sorting of a result set.
• Does not use or keep statistics in the manner of a traditional index.
• Cannot contain a column with a FILESTREAM attribute. Other columns in the table that are not used in the index can contain the FILESTREAM attribute.
• Also, when it comes to updating a table, you cannot update a table with a columnstore index.

More info here.

Do you want to learn SQL Server Reporting Services 2012 in record short time?

Learning SQL Server 2012 Reporting Services was released today by Packt publishers. This is my second book on Microsoft SQL Server Reporting Services (which may be called a second edition). It is enlarged to include Reporting Services Integration with SharePoint 2010. With 566 pages it is somewhat thicker than the first edition (Learning SQL Server 2008 Reporting Services). However, the book has a large number of screen shots like my first book and is somewhat thinner than most other books in this genre. The reviewers and technical editors have put in a lot of effort and time to make this happen.

I take this opportunity to thank the reviewers (Satya Shyam Jayanty and Ritesh Shah) for their meticulous reading of the manuscript; the technical editors, Azharuddin Sheik, Mausam Kothari, Varun Pius Rodrigues, and Lubna Shaikh and many others at Pakct Publishing for their excellent editing and Microsoft Forums' (MSDN, Technet and SharePoint) help for answering so many of my questions.

Summary of Hands-on tasks you learn:

• Install SQL Server 2012, SharePoint Server 2010, and configure Reporting Services both native and SharePoint Integrated on Windows 7
• Install SSDT and author reports, both local and remote, and deploy them to report servers
• Develop desktop and web applications by using Report Viewer controls
• Author different kinds of reports with the latest gadgets by working with Report Builder
• Manage access to the report server, report scheduling, report delivery, report uploads, and downloads with Report Manager
• Create Tabular Models with SQL Server Analysis Services and deploy them to SSAS Server using SSDT
• Author Power View Reports from your SharePoint Site and export them as Power Point files
• Learn to create Data Driven alerts in SharePoint and monitor them on SQL Agent on SSMS
• Work with Windows Azure Reporting Services using Windows Azure Portal and SSMS
• Write applications in SSIS and WPF to access reports and access native server reports from SharePoint Site

I hope this book is appreciated as much as the earlier one.

This book can now be bought from the publishers as well as other online book sellers:
http://www.amazon.com/Learning-Server-Reporting-Services-ebook/dp/B00DM23X5M/ref=sr_1_2?s=books&ie=UTF8&qid=1372453186&sr=1-2

If you are using SQL Server 2008 then this book is for you,

http://www.amazon.com/Learning-Server-2008-Reporting-Services/dp/1847196187/ref=sr_1_1?s=books&ie=UTF8&qid=1372453239&sr=1-1

 

The links in the book are available here:

http://hodentekmsss.blogspot.com/2013/06/links-in-book-learn-sql-server.html

 

Contents of my forthcoming book on SQL Server Reporting Services 2012

This book is totally hands-on, you learn by doing it all by yourselves in record time.

Install SQL Server 2012, SharePoint Server 2010, and configure Reporting Services both native and SharePoint Integrated on Windows 7

Install SSDT and author reports, both local and remote, and deploy them to report servers

Develop desktop and web applications by using Report Viewer controls

Author different kinds of reports with the latest gadgets by working with Report Builder

Manage access to the report server, report scheduling, report delivery, report uploads, and downloads with Report Manager

Create Tabular Models with SQL Server Analysis Services and deploy them to SSAS Server using SSDT

Author Power View Reports from your SharePoint Site and export them as Power Point files

Get a first hand experience with Power View Reports, get the full breadth and width of features

Don't know Power Shell. OK. Learn it here and apply it to SharePoint and Report Server APIs and WMI

Learn to create Data Driven alerts in SharePoint and monitor them on SQL Agent on SSMS

Work with SQL Azure Reporting Services using Windows Azure Portal and SSMS. Start signing up for the service end up enjoying the reports

Write applications in SSIS and WPF to access reports and access native server reports from SharePoint Site

For details follow this link here:

http://hodentek.blogspot.com/2013/04/my-next-book-sql-server-reporting.html