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