SQL 如何选择一个sql索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4347101/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select an sql index
提问by Fransis
How to select an sql index
如何选择一个sql索引
I have an table TabEmp with fields c_ID (primary key), and c_Emp_ID.
我有一个包含字段的表 TabEmpc_ID (primary key)和c_Emp_ID.
I created an index on it idx_TabEmp (non-clustered)with the fields c_ID (primary key)and c_Emp_ID
我在上面创建的索引idx_TabEmp (non-clustered)与字段c_ID (primary key)和c_Emp_ID
I am using select statement
我正在使用选择语句
select * from index = idx_TabEmp
It throws an error
它抛出一个错误
Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax
I am using
我在用
select * from TabEmp (index = idx_TabEmp)
it works, but I am not sure if that is the correct way to select an index
它有效，但我不确定这是否是选择索引的正确方法
Can you please tell me the correct way to query an index?
你能告诉我查询索引的正确方法吗？
回答by Sathyajith Bhat
The index is something which the optimizer picks up "automagically - ideally you don't need to force select an index.
索引是优化器“自动选择”的东西 - 理想情况下，您不需要强制选择索引。
If you really want to force select the index, use index hint
如果你真的想强制选择索引，使用索引提示
SELECT *
FROM TabEmp
WITH (INDEX(idx_TabEmp))
Also, note that with no filter conditions (ie, no WHEREclauses), the Index does not come into the picture since you are not searching for a specific data - you're selecting everything.
另外，请注意，如果没有过滤条件（即没有WHERE子句），索引不会出现在图片中，因为您不是在搜索特定数据 - 您正在选择所有内容。
To provide the book analogy - when you're reading a full book cover to cover - you don't need to look at the Index. It's only when you're searching for a specific page that you look at the index and find what you want.
提供书籍类比 - 当您阅读完整的书籍封面时 - 您不需要查看索引。只有当您搜索特定页面时，您才能查看索引并找到您想要的内容。
回答by Rajesh Chamarthi
This is the syntax for a table hint.
这是表提示的语法。
SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...]);
in sql server which I think is your case based on your error.
在 sql server 中，根据您的错误，我认为这是您的情况。
As to whether the index will be picked up or not (both in oracle and sql server) will depend on a lot of other reasons. As the name indicates, it is just a hintto the optimizer. The cost of the query using the hint and without the hint will eventually be the deciding factors for the optimizer.
至于索引是否会被拾取（在oracle和sql server中）将取决于很多其他原因。顾名思义，它只是对优化器的一个提示。使用提示和不使用提示的查询成本最终将成为优化器的决定因素。
In most cases, you'll not see the need to specify the hint.The optimizer used this access path if using the index is the best way to retrieve the data and all the meta data (statistics) indicate the same.
在大多数情况下，您不会看到需要指定提示。如果使用索引是检索数据的最佳方式并且所有元数据（统计数据）指示相同，则优化器使用此访问路径。
