Will It Compress?

  • Comments posted to this topic are about the item Will It Compress?

  • SPARSE is not a datatype but a property.

    So, the correct answer is wrong!

    SELECT * FROM sys.types

    WHERE is_user_defined='0'

  • "we attempt to compress the table using this statement: sp_estimate_data_compression_savings ..." is wrong because the sp "sp_estimate_data_compression_savings" just is an "estimate" and it doesn't compress anything!

  • This was removed by the editor as SPAM

  • Carlo Romagnano (9/2/2016)


    SPARSE is not a datatype but a property.....

    +1

    There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.

    The answer might as well have been 'fooled you, the server was unplugged.'

    At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right πŸ™‚


    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

  • BWFC (9/2/2016)


    Carlo Romagnano (9/2/2016)


    SPARSE is not a datatype but a property.....

    +1

    There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.

    The answer might as well have been 'fooled you, the server was unplugged.'

    At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right πŸ™‚

    +1

  • excellent question...

    But sparse column is not a data type its a property on a column, it was very hard to think that way, got the answer wrong but it was definitely a good learning though specifying sparse column would have made easier to answer..!!

  • +1 Sparse is not a datatype.

  • Carlo Romagnano (9/2/2016)


    SPARSE is not a datatype but a property.

    So, the correct answer is wrong!

    SELECT * FROM sys.types

    WHERE is_user_defined='0'

    THIS.

    I understand why Andy didn't specify SPARSE, as that would be a giveaway. I also understand why he didn't open it up to all properties/ attributes, as then it would get way too complex (do we count a sampling of the different possible lengths of CHAR() and NCHAR() for example?)

    So I think this is one of those interesting questions that doesn't work as a QotD.

  • Carlo, agree that "we attempt to compress the table using" should have been said differently.

  • Good morning all and happy Friday! I agree that sparse isn't a data type - funny how you can think it and write it and still miss the obvious. Clearly I could have done better. If it was "all datatypes and attributes" it would have been closer, but still tough to figure out. My goal is never to write a "gotcha", just to challenge you to see the whole board.

    Thanks for trying the question and all the comments, always appreciated.

  • I missed it for a different reason. I was guessing he intended sparse column so I knew it wouldn't compress but the statement isn't the cause of the error. The cause of the error is the column in the table so my thoughts were answer one and the last as correct. I had switch from answer two to one. Shoots self in foot for second guessing...

  • I do not regret that I have got it wrong. I learned something new, thanks Andy. πŸ™‚

    The question has been imprecise specified, because only SQL Server Enterprise Edition

    supports Data Compression and DB Engine SPROC sp_estimate_data_compression_savings.

    So e.g. in the MS SQL 2014 Standard command ends with errors Msg 534 and Msg 7738.

    At last, I did not consider Sparse column as the data type.

  • Carlo Romagnano (9/2/2016)


    SPARSE is not a datatype but a property.

    So, the correct answer is wrong!

    SELECT * FROM sys.types

    WHERE is_user_defined='0'

    I agree - SPARSE has nothing to do with type.

    Tom

  • sipas (9/2/2016)


    BWFC (9/2/2016)


    Carlo Romagnano (9/2/2016)


    SPARSE is not a datatype but a property.....

    +1

    There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.

    The answer might as well have been 'fooled you, the server was unplugged.'

    At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right πŸ™‚

    +1

    And + another 1

    Tom

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

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