I have a table and an indexed view on it like
Create table mytable1 (ID int identity(1,1), Name nvarchar(100))
Create table mytable2 (ID int identity(1,1), Name nvarchar(100))
Create view myview
with schemabinding
as
select a.name, b.name
from mytable1 a
join mytable2 b on a.Id = b.Id
Now if I run the following query
select a.name, b.name
from mytable1 a
join mytable2 b on a.Id = b.Id
It does not use my indexed view. Is there any hint (or other way) to force SQL Server to use indexed view instead?
I have a big system, and need to optimise it. I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.
I am using SQL Server 2014 Enterprise Edition.
Best Answer
I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.
Using your example, it looks like you created the view but did not actually create an index upon it.
Since there is no index on this view, we scan on the base tables:
But once we add an index, the optimizer can use it:
This appropriately used the view:
There is no hint or other method to force SQL Server to use an indexed view when it is not referenced in the query.
Additional information (from Geoff Patterson)
One extra point is that while the optimizer can, in Enterprise Edition only, use the indexed view in this case, it may make sense to directly reference the view using the
NOEXPAND
hint if you need to be 100% sure of the view index being used or if you ever want it to be used in Standard Edition.I've frequently seen queries even in Enterprise Edition where the optimizer does not pick up on the fact that the view index can be used unless
NOEXPAND
is used. It's more common with complex queries, but can happen with simple queries also.Paul White has one of the better articles I've read exploring the nuances of
NOEXPAND
; beyond just usage of the view index, the hint can also impact things like whether statistics are automatically created on the indexed view and cardinality estimates for the plan.And from Zane: As a side note, be careful with indexed views as like any other index it will add to your update, insert, and delete times.