Data Compression

  • p.shabbir

    SSCrazy

    Points: 2404

    Comments posted to this topic are about the item Data Compression

  • SQLRNNR

    SSC Guru

    Points: 281210

    Though i understood what the OP was trying to achieve I don't like this particular question and I don't like the supporting documentation.

    Vardecimal existed only in SQL 2005. SQL 2008 introduced compression (row and page). While vardecimal is a form of compression, the fact that it only exists in 2005 doesn't really match the documentation that was for SQL 2008.

    The documentation was also targeted at people looking to downgrade a database from Enterprise edition. If you use the query provided in the documentation and you have no enterprise only features in use in the database being queried, you will get nothing in your result set. That means it is a far stretch to see that you could find vardecimal as an enterprise feature. And if you are running SQL 2008, you will never be able to answer this question correctly if using that documentation as your means to determine enterprise features.

    This seems to be a better source for that information (2008 R2)

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

    And for the vardecimal feature in 2005, you could use this

    http://technet.microsoft.com/en-us/library/ms143761(v=sql.90).aspx

    All of that said, if people are answering questions based on current edition, this one should have specified that it was 2005 to avoid that conundrum.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

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

  • Sean Pearce

    SSCoach

    Points: 15750

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    +1

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Carlo Romagnano

    SSC-Insane

    Points: 21711

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    +1

  • sqlnaive

    SSCoach

    Points: 17435

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    +1

  • SADSAC

    SSCommitted

    Points: 1897

    SQLRNNR (11/12/2013)


    Though i understood what the OP was trying to achieve I don't like this particular question and I don't like the supporting documentation.

    Vardecimal existed only in SQL 2005. SQL 2008 introduced compression (row and page). While vardecimal is a form of compression, the fact that it only exists in 2005 doesn't really match the documentation that was for SQL 2008.

    The documentation was also targeted at people looking to downgrade a database from Enterprise edition. If you use the query provided in the documentation and you have no enterprise only features in use in the database being queried, you will get nothing in your result set. That means it is a far stretch to see that you could find vardecimal as an enterprise feature. And if you are running SQL 2008, you will never be able to answer this question correctly if using that documentation as your means to determine enterprise features.

    This seems to be a better source for that information (2008 R2)

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

    And for the vardecimal feature in 2005, you could use this

    http://technet.microsoft.com/en-us/library/ms143761(v=sql.90).aspx

    All of that said, if people are answering questions based on current edition, this one should have specified that it was 2005 to avoid that conundrum.

    Agreed wholeheartedly

  • ksatpute123

    Hall of Fame

    Points: 3325

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    +1

  • TomThomson

    SSC Guru

    Points: 104762

    Jason and Koen have pointed out the flaws in this question.

    I wonder how many people will end up answering (correctly) "developer edition" and finding out that that answer is treated as wrong.

    But there's another flaw - this one in the SQL Server product (we have a non-optional option, which is surely a flaw) not in the question:-

    Maybe there should be a none of the above answer; the question doesn't state what release it is about, so it should be assumed to be about 2008, 2008 R2, or 2012 (the ones currently fully released and on standard support); according to Jason none of those supports vardecimal, and if that's correct clearly no edition of any of them does. But looking at database properties using SSMS for sql 2012 or ssms 2008 R2 in developer edition provides a list which says the vardecimal storage option is enabled - I don't currently have a 2008 system to try it on, but I imagine it's the same - and that line is in weak type, which I think means you can't change it, so maybe Jason is wrong - the thing that isn't supported is disabling the feature, the feature itself is supported, so a "none of the above" answer isn't needed. Of course the idea that something which can't be switched off is listed as an option raises an interesting question: if you can't switch it off, it isn't optional so why say it's an option? The ways of Microsoft are indeed arcane. :w00t: And this isn't just a funny thing in SSMS: I can use

    sp_tableoption '<table>', 'vardecimal storage format' 1 (or 0)

    to tell the system whether an individual table is allowed to use the format, and

    select name, object_id from sys.objects where objectproperty(object_id, N'TableHasVarDecimalStorageFormat') = 1

    will then list or not list the table depending whether I set the property to 1 or to 0, so something really is going on there, it's not just SSMS being silly.

    Tom

  • Gary Varga

    SSC Guru

    Points: 82166

    SQLRNNR (11/12/2013)


    Vardecimal existed only in SQL 2005. SQL 2008 introduced compression (row and page). While vardecimal is a form of compression, the fact that it only exists in 2005 doesn't really match the documentation that was for SQL 2008.

    It was deprecated but not removed. See here.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Gary Varga

    SSC Guru

    Points: 82166

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    Also, even though it wasn't an option it is also in the Evaluation Edition.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • twin.devil

    SSC-Insane

    Points: 22208

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    ++1 🙂

  • Ed Wagner

    SSC Guru

    Points: 286957

    Koen Verbeeck (11/13/2013)


    First of all, this question is way too easy. Enterprise has all the features, so you don't even have to think.

    Secondly, developer is the same as enterprise feature-wise, so that would also have been a correct answer.

    Agree completely.

  • Gary Varga

    SSC Guru

    Points: 82166

    I wouldn't knock the fact that most of us found it an easy question. There are always people new to SQL Server and we should cater for them too.

    Perhaps it would have been helpful to state that currently all features exist in the Enterprise edition (as well as Developer and Evaluation editions). Then that would be valuable information especially as it would highlight that you need to be careful when using the Developer and Evaluation editions as someone can inadvertently use a feature that ties them to a more expensive edition than they may have planned for.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • SQLRNNR

    SSC Guru

    Points: 281210

    Gary Varga (11/13/2013)


    SQLRNNR (11/12/2013)


    Vardecimal existed only in SQL 2005. SQL 2008 introduced compression (row and page). While vardecimal is a form of compression, the fact that it only exists in 2005 doesn't really match the documentation that was for SQL 2008.

    It was deprecated but not removed. See here.

    Granted that it is there behind the scenes, but you can't change the setting as of SQL Server 2008. If you can't enable/disable the setting, then it is not available.

    The reason that it is still present is because it was replaced by page / row compression which use the same technique to compress as vardecimal did.

    All user databases are created by default with the vardecimal setting set to true since 2008. Whatever the reason being (whether it be related to row/page compression or forced backward compatibility), it's technically there. But it doesn't really matter because it can't be changed. And even with it enabled, the query in the source documentation for this question will not show vardecimal in the result set. Why? Because the feature is a ghost - it's not really there. 😀

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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