SQL Basic - Organizational Structure

Heap: Heap is a default organizational sturcture of sql server, heap organize data pages or extets when clustered index is not created on a table.  In heap rows insearted not in a particular order. As soon as a new row insert, it organize at the end of the data page and add pointer in IMAC page.

B-tree: B-Tree or Balanced Tree is most commanly used structure for organizing indexes in both clustered and non-clustered. In B-Tree pages are organized in a hierarchical tree structure.B-trees start with an IAM page that identifies where the first page of the B-tree is located, The first page of the B-tree is an index page, also know as root level page, contains key values and page addresses for the next pages in the index. Depending on the size of the index, the next level of the index may be data pages or additional index pages know as intermidate level indexes. The next level of pages below the root and intermediate levels of the indexes know as leaf level, contains all the data pages of index. In clustered index these data pages contains data itself or in non-clustered index these pages contain address of data pages actually stored.

Columnar / Column Store Index: This is new organizational structure introduced in sql server 2012. SQL Server 2012 introduced nonclustered columnstore indexes, Whereas SQL Server 2014 has both clustered and nonclustered columnstore indexes. Basically useful for data warehouse queries, not for OLTP system.

Few Limitations:
Column store index makes a table Read Only.
Column store index cannot be created on a view. Only one Column store Index is allowed.
Column store index cannot have more than 1024 columns.It cannot include Sparse Column.
Column store index definition cannot be changed using ALTER INDEX statement We need to drop and re-create instead.
Below datatypes are not supported by Column store index (binary, varbinary, text, ntext, varchar(max), nvarchar(max))
     
How it Works
In RowStore, all column values are stored in pages and then these pages become a segment. Whereas in ColumnStore, each column store in different segment to be consistent with clustered index.( a segement consist million of rows). The data in each column segment matches with row by row order by clustered index, that mean if a particular row found in a segment, same row value should be found on another column segment.

References:
https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/
http://www.pythian.com/blog/column-store-indexes-in-sql-server-2012/