Columnstore Indexes

  • dstrickrott

    SSCommitted

    Points: 1950

    Comments posted to this topic are about the item Columnstore Indexes

  • TomThomson

    SSC Guru

    Points: 104707

    Nice simple question.

    After getting the 23rd Feb question wrong because I forgot that 2012 didn't include clustered columnstore I found this one rather easy.

    Tom

  • Hany Helmy

    SSChampion

    Points: 13291

    Gr8 question, thanx 🙂

    Did anyone tried this feature before? And is there any actual performance gained?

  • patricklambin

    SSCrazy Eights

    Points: 9964

    As Tom Thomson wrote , it was easy to find the 2 good choices after the question of 02/23/2015.

    Moreover , I was helped by 2 sessions about Columnstore indexes during the SQL Server days and the TechDays 2014 ( I am happy to have a good remembrance ) even if it is not one of my favorite topics.

    So thanks Dawn Strickrott

  • Carlo Romagnano

    SSC-Insane

    Points: 21642

    Hany Helmy (3/15/2015)


    Gr8 question, thanx 🙂

    Did anyone tried this feature before? And is there any actual performance gained?

    I tried just a little. The answer is as usual "depends".

    If you query the whole table, there's a gain.

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    Can only create nonclustered columnstore index in SQL Server 2012

    This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂

    Anyway, I got it correct as only one single correct answer seemed a bit few.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    Carlo Romagnano (3/16/2015)


    Hany Helmy (3/15/2015)


    Gr8 question, thanx 🙂

    Did anyone tried this feature before? And is there any actual performance gained?

    I tried just a little. The answer is as usual "depends".

    If you query the whole table, there's a gain.

    The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those columns, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Emil B

    SSCertifiable

    Points: 5508

    Got it right! Playing euromillion today! (I work with 2008)

  • webrunner

    One Orange Chip

    Points: 29625

    Thanks for the question!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    Koen Verbeeck (3/16/2015)


    Can only create nonclustered columnstore index in SQL Server 2012

    This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂

    Yup, same interpretation here. I spent a few seconds trying to figure out if someone would be so tricky as to provide only one correct answer, and decided that they might.

    C'est la vie.

    🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    Jacob Wilkins (3/16/2015)


    Koen Verbeeck (3/16/2015)


    Can only create nonclustered columnstore index in SQL Server 2012

    This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂

    Yup, same interpretation here. I spent a few seconds trying to figure out if someone would be so tricky as to provide only one correct answer, and decided that they might.

    C'est la vie.

    🙂

    You have no faith in people 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    ..

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Koen Verbeeck (3/16/2015)


    Carlo Romagnano (3/16/2015)


    Hany Helmy (3/15/2015)


    Gr8 question, thanx 🙂

    Did anyone tried this feature before? And is there any actual performance gained?

    I tried just a little. The answer is as usual "depends".

    If you query the whole table, there's a gain.

    The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those columns, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.

    +1

    (fyi. just posted your statistical mode calculation post on linkedin, very helpful, thank for the article)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33257

    In the correct answer choice section, it should have "Clustered columnstore indexes were not available until SQL Server 2014" also as an additional choice apart of the 2 mentioned, I think. No?

    Thanks.

  • Hany Helmy

    SSChampion

    Points: 13291

    Stewart "Arturius" Campbell (3/16/2015)


    Koen Verbeeck (3/16/2015)


    Carlo Romagnano (3/16/2015)


    Hany Helmy (3/15/2015)


    Gr8 question, thanx 🙂

    Did anyone tried this feature before? And is there any actual performance gained?

    I tried just a little. The answer is as usual "depends".

    If you query the whole table, there's a gain.

    The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those indexes, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.

    +1

    Have used nonclustered columnstore indexes before, with tremendous performance gains; agree whoeheartedly with Koen's statement.

    Thanx 4 the info, but as far as I know nonclustered columnstore indexes cannot be updated in SQL 2012 (even in 2014 I guess) unless you perform a silly workaround of dropping the index, perform the DML operation on the table, then rebuild/create the index again! Or to use the switch/swap partitions method and both ways are just not practical for a 24/7 mission critical system running OLTP on some huge database.

    I think it is best suited for the enterprise data warehouse environments not OLTP.

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

    http://searchsqlserver.techtarget.com/feature/SQL-Server-2014-columnstore-index-the-good-the-bad-and-the-clustered

    Thanx.

Viewing 15 posts - 1 through 15 (of 24 total)

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