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
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?
SELECT *
FROM LegalActivity
WHERE UlaChoice = @UlaChoice
WITH(INDEX(X_Legal_Ula))
SELECT *
FROM LegalActivity
WHERE UlaChoice = @UlaChoice
WITH(INDEX(PK_Legal_ID))
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?
WITH
LOCK
FAST
OPTIMIZE FOR
MAXDOP
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: 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 (http://blog.sqlauthority.com)
Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
