Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
 
what is the use of select hint in sql server? why they use? can you give example
Posted 17-Nov-12 14:22pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Quote:
What is a Hint?
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.
 
Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).
 
There are three different kinds of hints. Let us understand the basics of each of them separately.
 
Join Hint
This hint is used when more than one table is used in a query. Two or more tables can be joined using different kinds of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.
 
Query Hint
This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query, as opposed to part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.
 
Table Hint
This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE are used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm and these hints are useful in those scenarios.
 
Let us run the following simple query with different kinds of query hints and observe the actual execution plan. The analysis of execution plan is not part of this article and will be covered in future.
 
USE AdventureWorks
GO
/* No Query Hint */
SELECT *
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Merge Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER MERGE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Hash Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER HASH JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Loop Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER LOOP JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Remote Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER REMOTE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
The above query will produce the following query execution plan.

 
i thinks this query really helpfull to u....
 
Thanks & Regard
ShamSmile | :)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 321
1 Sergey Alexandrovich Kryukov 286
2 RyanDev 75
3 PhilLenoir 70
4 nv3 60
0 Sergey Alexandrovich Kryukov 6,691
1 OriginalGriff 6,119
2 CPallini 2,473
3 Richard MacCutchan 1,697
4 Abhinav S 1,560


Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 17 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100