SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5
- by pinaldave
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 | Indiaplaza
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.
SQL Joes 2 Pros Development Series – Structured Error Handling
SQL Joes 2 Pros Development Series – SQL Server Error Messages
SQL Joes 2 Pros Development Series – Table-Valued Functions
SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters
SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options
SQL Joes 2 Pros Development Series – Introduction to Views
SQL Joes 2 Pros Development Series – All about SQL Constraints
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)
Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
