SQL SERVER – Quiz and Video – Introduction to Basics of a Query Hint

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Wed, 09 May 2012 01:30:42 +0000 Indexed on 2012/05/30 22:46 UTC
Read the original article Hit count: 420

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.

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

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)


Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about Joes 2 Pros

Related posts about PostADay