Columnstore Indexes

  • 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.

  • 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.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Hany Helmy (3/17/2015)


    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

    Columnstore indexes are meant for the enterprise DWH and not for OLTP, that's exactly the point.

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

  • Hany Helmy (3/15/2015)


    Gr8 question, thanx 🙂

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

    Yes. See http://www.sqlservercentral.com/stairway/121631/[/url]

    (Bit of work life balance issues at the moment, hope to get the next part finished soon)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I'm on SQL 2008, but will definitely be checking out columnstore indexes when we upgrade one of our servers. It's scheduled for this year.

  • Thanks for the question.

  • 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 🙂

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

    SO, The answer is wrong!

    "Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.

    Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx

    There is only one single correct answer!

  • lcajui (3/17/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 🙂

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

    SO, The answer is wrong!

    "Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.

    Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx

    There is only one single correct answer!

    +1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.

    It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.

    But in the end, none of us is perfect and I did learn something.

  • It was my intention that this be a correct answer. I can see how it could be interpreted the way you and others interpreted it. Maybe it would have been clearer, or at least the way I meant for it to be, if it was worded like this: "In SQL Server 2012, can only create nonclustered columnstore index"

  • RLilj33 (3/17/2015)


    lcajui (3/17/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 🙂

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

    SO, The answer is wrong!

    "Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.

    Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx

    There is only one single correct answer!

    +1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.

    It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.

    But in the end, none of us is perfect and I did learn something.

    I find it extremely frustrating to see people claiming that there's only one possible meaning to a statement which is plainly and clearly ambiguous, particularly since (a) it is a well-known property of the English language that it is extremely easy to write such obviously ambiguous statements and (b) it's a well known property of every other natural language too and (c) it is extremely common for such ambiguous statements to be written unintentionally.

    Tom

  • TomThomson (3/17/2015)


    RLilj33 (3/17/2015)


    lcajui (3/17/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 🙂

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

    SO, The answer is wrong!

    "Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.

    Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx

    There is only one single correct answer!

    +1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.

    It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.

    But in the end, none of us is perfect and I did learn something.

    I find it extremely frustrating to see people claiming that there's only one possible meaning to a statement which is plainly and clearly ambiguous, particularly since (a) it is a well-known property of the English language that it is extremely easy to write such obviously ambiguous statements and (b) it's a well known property of every other natural language too and (c) it is extremely common for such ambiguous statements to be written unintentionally.

    Ok, so we agree that not checking this answer is also correct. Then we can not be wrong being right 😀

Viewing 11 posts - 16 through 25 (of 25 total)

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