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.
- 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 (https://blog.sqlauthority.com)
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
Which of the following key word will force the query to use indexes created on views?
Answer:- b) SCHEMABINDING
Country :- INDIA
Hi pinal,
Nice Question… and I read that article also awesome!!! I am awaiting for getting the prize….
Thanks,
Vijayakumar P
Which of the following key word will force the query to use indexes created on views?
Answer:
c) NOEXPAND
Country : India
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
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.
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
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
C) NOEXPAND
country:india
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
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.
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
Which of the following key word will force the query to use indexes created on views?
C) NOEXPAND
Country USA
Question:
Which of the following key word will force the query to use indexes created on views?
Answer: NoExpand Option
Country: Pakistan