Post reply

Server Columnstore Index

  • February 26, 2012 at 5:40 am

    #257479

    hi,

    despite the fact i read some artical about this component,

    i still dont understand this component.

    what i am understand is:

    Server Columnstore Index -

    1) one or more column and populate it to page .

    2) the column is compress.

    3) the column in ram.

    as i undestand 🙂 untill here i am ok.

    but this exactly what i am doing when i make regular index chose column populate it to page with sorting data.

    so what i miss? what is all excitement 🙂

    thanks sharon

  • February 26, 2012 at 6:18 am

    #1451818

    have you read this article...it may help expiain

    http://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • February 26, 2012 at 6:28 am

    #1451819

    It's stored in a different way (google vertipak storage).

    Normal indexes are still stored by row. If you have an index on 3 columns, in the index pages those three columns appear in an index row. ColumnStore is different in that the data is stored per column, not per row.

    So if you had an index on LastName, FirstName, City, in a normal index that would appear on the pages as LastName, FirstName, City; LastName, FirstName, City; LastName, FirstName, City; LastName, FirstName, City; etc. On the columnstore it would appear (vastly simplified) as LastName, LastName, LastName, LastName, LastName; FirstName, FirstName, FirstName, FirstName, FirstName; City, City, City, City, City

    This leads to better compression and better performance if you're scanning a single column as you can just read the data you want, not the other columns that are mixed in (you can't seek a columnstore index).

    There's all sorts of other segment elimination, compression and similar tricks, plus the new batch-mode processing (only on columnstores in 2012) mean that they can be a lot faster for certain operations.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 26, 2012 at 6:48 am

    #1451821

    thank you vary muce for your explanation.

    so if i undersatnd there is no diffrent if i put one and only regular index on column

    or Columnstore indexes on one column ?

    ( i know there is many iisue depend when create Columnstore indexes but this is not iisues now :))

  • February 26, 2012 at 7:02 am

    #1451822

    There is a huge amount of difference between a columnstore index and a single column nonclustered index. The architectures are completely different (as you would have seen from the search results for the search term I listed), the compression is different, the storage is different, the segment elimination doesn't have a counterpart for nonclustered indexes, the batch-mode processing is only for columnstore indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply