SQL Server中的“选择提示"有什么用?他们为什么使用?你能举个例子吗 [英] what is the use of select hint in sql server? why they use? can you give example
问题描述
SQL Server中的选择提示"有什么用?他们为什么使用?您能否举个例子
Hi,
what is the use of select hint in sql server? why they use? can you give example
推荐答案
什么是提示?
提示是为DML语句上的SQL Server查询处理器指定用于强制执行的选项和强烈建议.这些提示会覆盖查询优化器为查询选择的任何执行计划.
在继续探讨这一主题之前，我们需要考虑一个非常重要的事实，并说几句谨慎的话. SQL Server查询优化器是一个非常智能的工具，它可以最佳地选择执行计划.在绝对必要时，应由经验丰富的开发人员尝试向Query Optimizer提供提示，这些开发人员确切地知道自己在做什么(或在开发中作为实验和学习的方式).
有三种不同的提示.让我们分别了解它们的基础.
加入提示
当查询中使用多个表时，将使用此提示.可以使用不同类型的联接来联接两个或多个表.此提示将强制使用连接算法的类型.联接可用于SELECT，UPDATE和DELETE语句.
查询提示
当必须将某种逻辑应用于整个查询时，将使用此提示.查询中使用的任何提示都将应用于整个查询，而不是部分查询.无法指定仅将查询的特定部分与提示一起使用.在执行任何查询之后，将指定OPTION子句以将逻辑应用于此查询.查询始终具有以下任何语句:SELECT，UPDATE，DELETE，INSERT或MERGE(SQL 2K8)；并且此提示可以应用于所有这些提示.
表提示
当必须控制表的某种锁定机制时使用此提示.当使用任何Transact SQL操作SELECT，UPDATE，DELETE，INSERT或MERGE时，SQL Server查询优化器总是在表上施加适当类型的锁.在某些情况下，开发人员知道何时何地应覆盖锁定算法的默认行为，并且这些提示在那些情况下很有用.
让我们使用不同种类的查询提示运行以下简单查询，并观察实际的执行计划.执行计划的分析不是本文的一部分，以后将进行介绍.
使用AdventureWorks
GO
/*无查询提示*/
选择*
FROM Production.Product AS p
内联Sales.SalesOrderDetail AS p.ProductID = sod.ProductID
体重= 20.77
GO
/*合并联接查询提示*/
选择*
FROM Production.Product AS p
内部合并联接Sales.SalesOrderDetail AS p.ProductID = sod.ProductID
体重= 20.77
GO
/*哈希联接查询提示*/
选择*
FROM Production.Product AS p
内部哈希联接Sales.SalesOrderDetail AS p.ProductID = sod.ProductID
体重= 20.77
GO
/*循环联接查询提示*/
选择*
FROM Production.Product AS p
内部循环连接Sales.SalesOrderDetail AS p.ProductID = sod.ProductID
体重= 20.77
GO
/*远程联接查询提示*/
选择*
FROM Production.Product AS p
内部远程联接Sales.SalesOrderDetail AS p.ProductID = sod.ProductID
体重= 20.77
GO
上面的查询将产生以下查询执行计划.
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
Sham:)
这篇关于SQL Server中的“选择提示"有什么用?他们为什么使用?你能举个例子吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
