SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5

SQL SERVER - Expanding Views - Contest Win Joes 2 Pros Combo (USD 198) - Day 4 of 5 combos

August 2011 we ran a contest where every day we give away one book for an entire month. The contest had extreme success. Lots of people participated and lots of give away. I have received lots of questions if we are doing something similar this month. Absolutely, instead of running a contest a month long we are doing something more interesting. We are giving away USD 198 worth gift every day for this week. We are giving away Joes 2 Pros 5 Volumes (BOOK) SQL 2008 Development Certification Training Kit every day. One copy in India and One in USA. Total 2 of the giveaway (worth USD 198). All the gifts are sponsored from the Joes 2 Pros.

The books are available here Amazon | Flipkart 

How to Win:

  • Read the Question
  • Read the Hints
  • Answer the Quiz in Contact Form in following format
    • Question
    • Answer
    • Name of the country (The contest is open for USA and India residents only)
  • 2 Winners will be randomly selected announced on August 20th.

Question of the Day:

Which of the following key word will force the query to use indexes created on views?

a) ENCRYPTION
b) SCHEMABINDING
c) NOEXPAND
d) CHECK OPTION

Query Hints:

BIG HINT POST

Usually, the assumption is that Index on the table will use Index on the table and Index on view will be used by view. However, that is the misconception. It does not happen this way. In fact, if you notice the image, you will find the both of them (table and view) use both the index created on the table. The index created on the view is not used. The reason for the same as listed in BOL.

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don’t initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn’t reference the view. If they are close, this may give you the confidence that the decision of whether or not to use the indexed view doesn’t matter.

Additional Hints:

I have previously discussed various concepts from SQL Server Joes 2 Pros Volume 4.

Next Step:

Answer the Quiz in Contact Form in following format

  • Question
  • Answer
  • Name of the country (The contest is open for USA and India)

Bonus Winner

Leave a comment with your favorite article from the “additional hints” section and you may be eligible for surprise gift. There is no country restriction for this Bonus Contest. Do mention why you liked it any particular blog post and I will announce the winner of the same along with the main contest.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Joes 2 Pros
Previous Post
SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5
Next Post
SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5

Related Posts

14 Comments. Leave new

  • Question:
    Which of the following key word will force the query to use indexes created on views?

    Answer:
    c) NOEXPAND

    Country : India

    Favorite Article :
    SQL Joes 2 Pros Development Series – Introduction to Views

    Reply
  • Nikunj Nandaniya
    August 9, 2012 10:52 am

    Which of the following key word will force the query to use indexes created on views?
    Answer:- b) SCHEMABINDING
    Country :- INDIA

    Reply
  • Vijayakumar P
    August 9, 2012 2:25 pm

    Hi pinal,

    Nice Question… and I read that article also awesome!!! I am awaiting for getting the prize….

    Thanks,
    Vijayakumar P

    Reply
  • Which of the following key word will force the query to use indexes created on views?

    Answer:
    c) NOEXPAND

    Country : India

    Reply
  • Ans: C) NOEXPAND
    Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

    https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

    Reply
  • Ans: c)NOEXPAND

    Additional hints:-

    SQL Joes 2 Pros Development Series – Introduction to Views

    in that particular article mostly i can see you there for every user comment’s and you only followed up them with the corrected options..i was really enjoying this article..!!!

    Thanks
    Ganesh N
    India.

    Reply
  • Debbie Schoenick
    August 11, 2012 7:23 am

    My favorite article. – table Value stored procedure parameters – something I sure wish we could use at our company but we are only on the 2005 edition of SQL

    Reply
  • Hi Pinal,

    I liked your blog
    SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options

    In this blog you have mentioned well about check option, how can we avoid trigger and use this.

    Best Regards,
    S Kumar

    Reply
  • C) NOEXPAND
    country:india

    Reply
  • P.Anish Shenoy
    August 13, 2012 12:55 pm

    Hi Sir,
    SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options

    is one of my favourite articles. Check option was something that i learned new from the blog post.

    Regards,
    P.Anish Shenoy,
    Bangalore

    Reply
  • Kalyanasundaram.K
    August 13, 2012 8:22 pm

    Hi Pinal,

    My most favorite link from additional hints as below :
    SQL Joes 2 Pros Development Series – Table-Valued Functions

    Reason : Last Year i am participated this quiz, i got a book SQL Programming Joes 2 Pros Volume -4 for posting answer to this link. Also, its one of the re-usuability option in function in SQL with some parameters.

    Reply
  • Uday Bhoopalam
    August 14, 2012 2:00 am

    Question:
    Which of the following key word will force the query to use indexes created on views?

    Answer: NoExpand Option will force the query to use indexes on the view. Noexpand applies only to Indexed views. Table hints override the default behavior of the query optimizer for the duration of the DML Statement.

    Country: USA

    Reply
  • Which of the following key word will force the query to use indexes created on views?

    C) NOEXPAND

    Country USA

    Reply
  • Question:
    Which of the following key word will force the query to use indexes created on views?

    Answer: NoExpand Option
    Country: Pakistan

    Reply

Leave a Reply