Introduction
Here i am going to introduce new feature of SQL-2012
SQL Server 2012 introduced a new feature Columnstore Index which can be
used to improve query performance. It is used in data warehousing.
Description
The property of column storage is the ability to read the values of a
particular column of a table without having to read the values of all the
other columns. In row-oriented storage this is impossible because the individual
column values are physically stored grouped in rows on the pages and reading a
page in order to read a column value must fetch
the entire page in memory, thus automatically reading all the other columns in
the row.
There are two kind of storage in database-Row Store and Column Store
Row Store
: Row store stores rows of data on a page
Column Store:
Column store stores all the data in a column on the same page
These columns are much easier to search – instead of a query searching all the
data in an entire row whether the data is relevant or not, column store queries
need only to search much lesser number of the columns. This means major
increases in search speed and hard drive use.
Column store indexes are heavily compressed, which translates to even greater
memory and faster searches.
Creating a ColumnStore index:
You can create a columnstore index using the following ways:
1.
Using syntax for creating columnstore index:
To create a
ColumnStoreIndex we need to specify the keyword “COLUMNSTORE” and enter the data
as we normally do. Once we add a column store to a table, we cannot delete,
insert or update the data – it is READ ONLY.
A columnstore index stores each column in a separate set of disk pages, rather
than storing multiple rows per page as data traditionally has been stored.
Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX <IndexName> ON <TableName>(Col1,Col2,....Coln
Example
Step 1
Create a table
CREATE TABLE EMPLOYE1 (E_NO(VARCHAR2(20)),E_NAME(VARCHAR2(50)),SAL(NUMERIC(20)));
Step 2
Creating nonclustered index on the table
CREATE NONCLUSTERED INDEX INDEX_1 ON EMPLOYE1(E_NO.E_NAME,SAL)
2. You can also create a columnstore index using Object Explorer in Management Studio
·
Expand the tree structure for the table and then right click on the Indexes
icon.
·
Select New Index and then Nonclustered columnstore index
·
Click Add in the wizard and it will give you a list of columns with check
boxes.
·
You can either choose columns individually or click the box next to Name at the
top, which will put checks next to all the columns.
·
Click OK.