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
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'.
What is the correct syntax in sql server with hints (tuning), that do the best run, as I have described?
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'.
Something like
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).
Need samples, please.
Thanks :)