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.

• Expecting Seek and getting a Scan
• Creating an index for improved optimization
• Implementing the query hint

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.

Quiz

1) You have the following query:

DECLARE @UlaChoice TinyInt
SET @Type = 1
SELECT *
FROM LegalActivity
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?

1. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   WITH(INDEX(X_Legal_Ula))
2. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   WITH(INDEX(PK_Legal_ID))
3. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   OPTION (Optimize FOR(@UlaChoice = ‘YP101′))

2) You have the following query:

SELECT *
FROM CurrentProducts
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?

1. WITH
2. LOCK
3. FAST
4. OPTIMIZE FOR
5. MAXDOP
6. READONLY

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.

Solution

1) 3

2) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)