Size of view

  • karthik babu

    Hall of Fame

    Points: 3312

    Comments posted to this topic are about the item Size of view

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • TomThomson

    SSC Guru

    Points: 104772

    Actually none of the options is correct. The third is just plain wrong and the others each omit the first column (name) from the returned row.

    What is actually returned will be

    vwShowConsolidatedReport NULL, NULL, NULL, 0 KB, 0 KB

    So the second option is the nearest to correct. Fortunately the current query/answer thinks the second option is correct, rather than either of the others.

    It's quite a nice question, apart from the error.

    Tom

  • SQLRNNR

    SSC Guru

    Points: 281205

    TomThomson (7/25/2015)


    Actually none of the options is correct. The third is just plain wrong and the others each omit the first column (name) from the returned row.

    What is actually returned will be

    vwShowConsolidatedReport NULL, NULL, NULL, 0 KB, 0 KB

    So the second option is the nearest to correct. Fortunately the current query/answer thinks the second option is correct, rather than either of the others.

    It's quite a nice question, apart from the error.

    Well, if the view does not exist or is in a different schema, then the third option is correct even though it truncated part of the error. Up until "database", it is verbatim correct if that view does not exist in the current database.

    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

  • TomThomson

    SSC Guru

    Points: 104772

    SQLRNNR (7/25/2015)


    TomThomson (7/25/2015)


    Actually none of the options is correct. The third is just plain wrong and the others each omit the first column (name) from the returned row.

    What is actually returned will be

    vwShowConsolidatedReport NULL, NULL, NULL, 0 KB, 0 KB

    So the second option is the nearest to correct. Fortunately the current query/answer thinks the second option is correct, rather than either of the others.

    It's quite a nice question, apart from the error.

    Well, if the view does not exist or is in a different schema, then the third option is correct even though it truncated part of the error. Up until "database", it is verbatim correct if that view does not exist in the current database.

    Yes, the 3rd one is right if the view doesn't exist in the db or is in the wrong schema.

    Tom

  • Toreador

    SSChampion

    Points: 11242

    SQLRNNR (7/25/2015)


    Well, if the view does not exist or is in a different schema, then the third option is correct even though it truncated part of the error. Up until "database", it is verbatim correct if that view does not exist in the current database.

    But the question says "vwShowConsolidatedReport is a non-indexed view", it would be a pretty mean question if they meant it was a view in a different database!

  • This was removed by the editor as SPAM

  • djj

    SSCoach

    Points: 18738

    I am no expert so I thought not including the single quote would give an error. In 2008R2 I got

    sp_spaceused (myView)

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'myView'.

    What I did not think about was that the parentheses are not needed and that it needs N'myView'.

  • This was removed by the editor as SPAM

  • djj

    SSCoach

    Points: 18738

    Bad eyesight. 😉

    Thought the brace was a paren.

  • chgn01

    Hall of Fame

    Points: 3537

    Every Problem has a Solution; Every Solution has a Problem: Smile:-P

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • gg 33882

    SSCommitted

    Points: 1836

    I found the Microsoft page referenced in the answer and read it through but assumed that 'Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database' meant that you would get an error if you tried to run it on a non-indexed view, so got it wrong 🙁

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    Got it correct. But Most of the time I have observed that the answer may appear diff in diff cases.

    Thanks.

  • SQLRNNR

    SSC Guru

    Points: 281205

    Toreador (7/27/2015)


    SQLRNNR (7/25/2015)


    Well, if the view does not exist or is in a different schema, then the third option is correct even though it truncated part of the error. Up until "database", it is verbatim correct if that view does not exist in the current database.

    But the question says "vwShowConsolidatedReport is a non-indexed view", it would be a pretty mean question if they meant it was a view in a different database!

    Agreed, but not anything we haven't seen done before.

    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

  • Neil Burton

    SSC-Insane

    Points: 21998

    SQLRNNR (7/27/2015)


    Toreador (7/27/2015)


    SQLRNNR (7/25/2015)


    Well, if the view does not exist or is in a different schema, then the third option is correct even though it truncated part of the error. Up until "database", it is verbatim correct if that view does not exist in the current database.

    But the question says "vwShowConsolidatedReport is a non-indexed view", it would be a pretty mean question if they meant it was a view in a different database!

    Agreed, but not anything we haven't seen done before.

    Definitely, there are some setters [cough] Andy Warren... who set cracking educational questions with a nasty sting. I recall one in particular that involved the inference from the use of 2012 that there was also cloud storage around. I went through the same thought process as gg33882. None of the answers match what I should get, the documentation says it works on indexed views therefore I think the answer is that it throws an error.

    To be fair to the setter I did learn something from the question that I will probably use in the future which is the intent of the QOTD.


    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the interesting question.

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

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