Columnstore index attempt in sql Server 2012

Brief introduction

After half a day of effort, today finally installed the SQL Server2012. Follow the new information in MSDN (Columnstore Indexes for Fast DW QP SQL Server 11). Tried the next Columnstore Index. Columnstore index is shown in its literal meaning. Is the index that is based on the column store. The concept is shown in Figure 1.

Figure 1. Columnstoreindex and row-based index comparisons

Columnstoreindex are stored in the page according to the column, rather than in the traditional behavior unit. The benefits can therefore be summed up as follows:

Previous data is stored in rows, and a single column in the Select will extract the entire row's page, using a column based index to extract only the columns following the SELECT. Improved performance.

Compression easier

Cache hit rates are greatly increased because, in a column of storage units, more pages can be stored in the cache (caching commonly used columns rather than entire rows)

Microsoft claims to be the first major database to support "pure" column storage. Other databases I do not know much about, have know the classmate can refute ...

Using the Columnstore index cannot be as restrictive as using other nonclustered indexes, and the Columnstoreindex restrictions are as follows:

1. A table can only have one columnstore Index

2. Cannot use filter index

3. Index must be partition-aligned

4. The indexed table becomes a read-only table

5. Indexed columns cannot be computed columns

6. Cannot use include keyword

As you can see, small and medium OLTP environments are largely out of the reach of this feature. Columnstore index appears to be suitable for OLAP and read-write separation.

Now let's take a look at some examples of using Columnstore index

Establish Columnstore index and make changes to the table data for Columnstore index

The establishment of Columnstore index and the establishment of normal nonclustered indexes seem almost indistinguishable, with just an additional columnstore keyword, as shown in Figure 2.

Figure 2. Failed to insert data after the table Columnstore index was established