This blog post is inspired from SQL Architecture Basics Joes 2 Pros: Core Architecture concepts – SQL Exam Prep Series 70-433 – Volume 3.
This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Basics of a Query Hint – A Primer. In the article we discussed various basics terminology of the query hints. The article further covers following important concepts of query hints.
Above three are the most important concepts related to query hint and SQL Server. There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced concepts. Let us have small quiz and check how many of you get the fundamentals right.
1) You have the following query:
DECLARE @UlaChoice TinyInt
SET @Type = 1
WHERE UlaChoice = @UlaChoice
You have a nonclustered index named IX_Legal_Ula on the UlaChoice field. The Primary key is on the ID field and called PK_Legal_ID 99% of the time the value of the @UlaChoice is set to ‘YP101’. What query will achieve the best optimization for this query?
2) You have the following query:
WHERE ShortName = ‘Yoga Trip’
You have a nonclustered index on the ShortName field and the query runs an efficient index seek. You change your query to use a variable for ShortName and now you are using a slow index scan.
What query hint can you use to get the same execution time as before?
Now make sure that you write down all the answers on the piece of paper.
Watch following video and read earlier article over here. If you want to change the answer you still have chance.
Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.
Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?
Reference: Pinal Dave (http://blog.sqlauthority.com)