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.
if object_id(N'mytable1') is not null
drop table mytable1
if object_id(N'mytable2') is not null
drop table mytable2
Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
insert into mytable1 values ('steve')
insert into mytable1 values ('jack')
insert into mytable1 values ('mike')
insert into mytable1 values ('ralph')
insert into mytable1 values ('simon')
insert into mytable2 values ('smith')
insert into mytable2 values ('jackson')
insert into mytable2 values ('mikaelson')
insert into mytable2 values ('montalvo')
insert into mytable2 values ('singer')
if object_id(N'myview') is not null
drop view myview
Create view myview
select a.id, a.name1, b.name2
from dbo.mytable1 a
join dbo.mytable2 b on a.Id = b.Id
select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
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:
CREATE UNIQUE CLUSTERED INDEX [ix_cl_names] ON [myview]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
This appropriately used the view:
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.
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.