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

This blog post is inspired from SQL Architecture Basics Joes 2 Pros: Core Architecture concepts – SQL Exam Prep Series 70-433 – Volume 3.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

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

Solarwinds

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.

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

Available at

USA: Amazon

India: Flipkart | IndiaPlaza

Volume: 1, 2, 3, 4, 5

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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Quiz and Video – Introduction to SQL Server Security
Next Post
SQL SERVER – Quiz and Video – Introduction to SQL Error Actions

Related Posts

3 Comments. Leave new

Leave a Reply

Menu