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

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 Koenig Training Solution and 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 (http://blog.sqlauthority.com)

About these ads

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

  1. 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

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

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

    Answer:
    c) NOEXPAND

    Country : India

  4. 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.

  5. Pingback: SQL SERVER – Contest – Summary of 5 Day and Additional Information « SQL Server Journey with SQL Authority

  6. 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

  7. 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

  8. 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

  9. 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.

  10. 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

  11. Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

  12. Pingback: Here comes my Free Copy of Joes 2 Pros Combo – Courtesy Pinal Dave « Datta's Ramblings on Business Intelligence 'N' Life

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

    Answer: NoExpand Option
    Country: Pakistan

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s