问题描述
我在sql server 2012中创建了一个视图，例如:
I created a view in sql server 2012, such as:
create myview as
select mytable2.name
from mytable1 t1
join myTable2 t2
on t1.id = t2.id
我希望联接table1和table2将具有正确的索引(id)，但是当我这样做时:
I want that join table1 and table2 will be with correct index (id), but when I do:
select * from myview
where name = 'abcd'
我希望最后一个选择将是列名称"的索引.
I want that the last select will be with index of column 'name'.
在SQL Server中，带有提示(调整)的正确语法是什么，正如我所描述的那样，该方法能最好地运行?
What is the correct syntax in sql server with hints (tuning), that do the best run, as I have described?
我只想强制将索引用于联接目的(列= id)，并在执行时强制使用索引名称:
I want to force using of index for join purpose only (the column = id), and forcing index name when doing:
select name from myview
where name = 'abcd'.
类似
create myview as
select mytable2.name
/* index hint name on column name */
from mytable1 t1
join myTable2 t2
/* index hint name on column id - just for join */
on t1.id = t2.id
我不想强迫最终用户在执行视图时使用视图添加提示-只需为他带来带有适当索引提示的视图即可.(或者，如果不可能，我该怎么做).
I don't want to force end-user that uses the view add hint when doing the view - just bring him the view as his with proper index hints.(or, if it is not possible - how can I do that).
请提供样品.
谢谢:)
推荐答案
SqlServer中的索引是从两组列中构建的.在表B(过滤器列，排序列)上创建索引IX，包括(包括其他列).
Indexes in SqlServer are built from two sets of columns.Create index IX on table B (Filter Columns,Sorting Columns) INCLUDE (Additional columns to be included).
从视图中进行选择时，优化器将在引用的表上合并索引.
And when selecting from views, the optimizer will incorporate indexes on the referenced tables.
第一组是索引表本身.最佳做法是先放置您要进行过滤的列，然后再放置您进行排序的列.第二组(包含)是添加到索引表中的其他列，因此所需的所有数据都在索引中(以防止键查找-取决于表设计).
The first set is the indexing table itself. Best practice is to place the columns by which you filter first, and then the columns by which you sort.The second set (Include), are additional columns you add to the indexing table, so all the data you require is in the index (to prevent key look ups - dpending on your table design).
在您的情况下，订单将为1)按名称转到MyTable2，并获取所有匹配的ID.2)使用步骤1中的ID，在Mytable1中找到匹配的ID.
In your case, the order will be1) Go to MyTable2 by name, and get all of the matching ID's.2) With the Id's from step 1, find the matching ID's in Mytable1
您的索引应为:
1)Table2(Name，ID)或Table2(Name)Include(ID)上的索引
1) An index on Table2(Name,ID) or Table2(Name)Include(ID)
2)表1(ID)上的索引
2) An index on Table1(ID)
在这种情况下，不应使用任何提示.通常，您应该避免使用提示.
There shouldn't be any hint used in this case.And in general, you should avoid using hints.
这篇关于在SQL Server中进行调整-视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！