none
View Table Question RRS feed

  • Question

  • Hello;

    I have a question about view tables. You can see question at following picture. I know that answer is A ( You should add a clustered index to the SalesV view) but question provider's answer is C.

    I want to sure for answer. Anyone help me? I m confused.

    thanks


    otaskiran

    Monday, October 22, 2012 8:18 AM

Answers

  • Answer C is incorrect.  However, it is likely this came straight from a MS exam and the answer there is wrong also. 

    From the referenced explanation.  http://msdn.microsoft.com/en-us/library/ms190174

    Indexed Views

    An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

    Monday, October 22, 2012 7:09 PM
    Moderator

All replies

  • The provider's answer is complete gibberish! Are you sure that this question was for SQL Server and not some other product? :-) Also note the incorrect syntax in the CREATE VIEW statement.

    Then again, I don't think your suggested answer is correct either. To wit, if memory serves, you need to add COUNT_BIG(*) to the view before you can create the index.

    What sort of test was this? One of Microsoft's official exams or something else?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 22, 2012 8:38 AM
  • Yeah you are right COUNT_BIG(*) is required for indexed views. But main question is "How we can store data in disk for order to improve performance".

    This question for 70-461.


    otaskiran

    Monday, October 22, 2012 8:42 AM
  • A is not correct as you can't add a clustered indesx into this View.

    What the question is trying to test, I believe, is the condition for how to create an indexed view. As the original view has used the GROUP BY clause, you were not able to create a clustered index on the view until you drop the view and recreate the view with Count_BIG(*) aggregation in the SELECT list.

    I hope this clears you doubt. Thanks.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!



    Monday, October 22, 2012 8:43 AM
  • no option seems to me correct. can you post complete question..?
    Monday, October 22, 2012 8:56 AM
  • All question is that.

    You can add COUNT_BIG(*) column via ALTER view operation.

    I have tried it now and it successfully completed. I created unique clustered index on view.

    I guess question expression and answers is not enough. 

    I will enter to 70-461 exam tomorrow. I hope Microsoft's original questions have enough expression and answer.


    otaskiran

    Monday, October 22, 2012 9:01 AM
  • This question for 70-461.

    But it is not part of 70-461, is it? Since you are taking the exam tomorrow (good luck!), I assume that this is from a training kit. Is this is from an official kit from Microsoft?

    The reason I ask is that if this comes from Microsoft, I'd like to make an alarm through the channels I have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 22, 2012 10:22 AM
  • This is not from Microsoft's offical kit. Because it will release at November.

    This is from my study materials. This is not part of 70-461.


    otaskiran

    Monday, October 22, 2012 10:50 AM
  • Thanks! If it's from a third party, I hold my horses.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 22, 2012 11:33 AM
  • Answer C is incorrect.  However, it is likely this came straight from a MS exam and the answer there is wrong also. 

    From the referenced explanation.  http://msdn.microsoft.com/en-us/library/ms190174

    Indexed Views

    An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

    Monday, October 22, 2012 7:09 PM
    Moderator
  • Thank you Tom.

    I m very very sure for correct answer A now.

    Regards.


    otaskiran

    Monday, October 22, 2012 7:13 PM