0

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 :)

Eitan
  • 1,286
  • 2
  • 16
  • 55

2 Answers2

2

I reckon creating an Index on the Name column would use the index, when selecting from view with the above shown where clause, you dont have to explicitly give any query hints to make that view use the index.

Index should be something like...

Index

CREATE NONCLUSTERED INDEX [IX_MyTable1_Name] 
ON [dbo].[myTable2] ([CompanyName] ASC)
GO

View Definition

CREATE VIEW myview 
 AS
SELECT t2.name  --<-- Use alias here since you have alised your table in from clause
FROM mytable1 t1
INNER JOIN myTable2 t2 ON t1.id = t2.id
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • No. What I meant is that I want to force using of index or join, as my previous comment (like select name with(index(myindex_name)) from myview, but there isn't such a syntax. What is the correct syntax doing that? – Eitan Jan 25 '15 at 12:58
  • Its not recommended but yes you can do that just use `WITH (INDEX([IX_MyTable1_Name]))` in your join clause just after the table name. – M.Ali Jan 25 '15 at 13:06
  • Yes, but I cannot use it for view, and I want to using simultaneously IX_Name (for the view select - don't know how doing that), and IX_ID for the join (or say that it is join, and the index hint is only for the join). In brief - IX_ID for join only, but IX_NAME for view (what is the syntax for view)? – Eitan Jan 25 '15 at 13:15
  • I want to use hint before selecting from view, because I want to bring the view as is (it has specific column, which I would like to select fast forcing use a specific index on it, when doing the select). Nevertheless, 'select name with(index(IX_MyTable1_Name)) from myview' is not the correct syntax. – Eitan Jan 25 '15 at 13:23
1

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).

In your case, the order will be 1) 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

Your indexes should be :

1) An index on Table2(Name,ID) or Table2(Name)Include(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.

Amir Pelled
  • 591
  • 4
  • 13
  • What I meant I want to force that the select will be with a specific index (The origin sql is about 100 lines). Something like 'with index()' in 'Select name with(index(myindex_forname)) from MyView' (myindex_forname = index for 'name' column) but there is not such a syntax. Also, table1 and table2 are connected with 'ID' column - If I add 'with (index(myindex_forid)) (myindex_forid = index for 'id' column). I need to know that syntax first - I didn't find the right syntax doing that. Thanks :) – Eitan Jan 25 '15 at 12:56