Columnstore Index metadata

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Comments posted to this topic are about the item Columnstore Index metadata


    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/

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers: 6%

    Incorrect answers: 94%

  • This was removed by the editor as SPAM

  • Neil Burton

    SSC-Insane

    Points: 22266

    Eirikur Eiriksson (8/27/2015)


    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers: 6%

    Incorrect answers: 94%

    I suspect that's got something to do with how people have researched this. If you don't specifically ensure you're checking the 2012 documentation it's very easy to come away with the wrong answer. A very cunning question Hugo; it's been a double learning exercise, thank you. I also have to say there are absolutely no tricks here, I got it wrong because I didn't check something I should have.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Ed Wagner

    SSC Guru

    Points: 286988

    Thanks for a very good question. I'm still on SQL 2008, so I had to do research to find the answer. Of course, I love starting the day by learning something new.

  • BarbW

    SSCarpal Tunnel

    Points: 4759

    Lost the point on a technicality - LOL! sys.column_store_row_groups not supported in SQL 2012.

  • AlexSQLForums

    SSChampion

    Points: 14254

    Got me good.

    and i thought there were no DMV's in 2012 for column index info.

    Alex S
  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    Burned! I don't have a SS 2012 instance so I ran some tests on 2014 and hoped the answer would be the same. I got the correct answer for 2014, though.

  • Ken Wymore

    SSCoach

    Points: 16666

    I was so sure I was going to get this right. Should have made sure things were the same in 2012 and 2014.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Thanks, all, for the kind words!

    And while I realize that this may be perceived as a trick question, that was not my intention - I submitted this question to point out an inconsistency that can catch you off guarad if you are not aware of it. This can especially burn people working in a mixed environment, or consultants who constantly move between versions as they move from client to client.


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Thanks for the question.

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

  • TomThomson

    SSC Guru

    Points: 104773

    BWFC (8/27/2015)


    Eirikur Eiriksson (8/27/2015)


    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers: 6%

    Incorrect answers: 94%

    I suspect that's got something to do with how people have researched this. If you don't specifically ensure you're checking the 2012 documentation it's very easy to come away with the wrong answer. A very cunning question Hugo; it's been a double learning exercise, thank you. I also have to say there are absolutely no tricks here, I got it wrong because I didn't check something I should have.

    Actually it's very easy to come up with the wrong answer if you look at 2012 documentation - since the right answer is listed in 2012 BOL as a "Catalog View" (specifically an Object Catalog View) and there is indeed no mechanism for getting the information using anything listed as a Dynamic Management View.

    So if we believe BOL the correct answer is Option 1, "None, this information is not exposed through DMVs" and Option 4 is wrong because that system view is NOT a DMV. (The BOL classification of system views can be seen at System Views (Transact-SQL).)

    So SQL 2012 BOL clearly says that this can't be done with a DMV. Anyone who tried to look this up in SQL 2012 BOL was guaranteed to get a "wrong" answer. People would be far more likely (1 chance in 8 instead of no chance at all) to pick an answer at random rather than look in 2012 BOL.

    But I don't really complain about the question - if I'd known the answer I wouldn't have had to look it up, and if the question had been consistent with BOL I wouldn't have noticed that "DMV" is yet another ill-defined SQL Server term.

    edit (2015/09/08): Steve has corrected the question to say "system view" insteaad of "DMV".

    Tom

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I've got a lot to learn about a columnstore index.

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Thanks for this question.

    I have been able to give the good answer only because I remembered a SQL Server Days session about différences between columnstore indexes in SQL Server 2014 and in SQL Server 2012. The speaker learnt me that there were 2 DMV giving the repartition in row groups in 2014 instead only one on 2012. So the answer was evident.

    I must admit that I have not deeply studied columnstore indexes in the 2012 version and not all in the 2014 one.

Viewing 14 posts - 1 through 14 (of 14 total)

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