Indexed view in SQL Server

Question

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.

Answer 1

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
go

Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
GO
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
GO

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')
go

if object_id(N'myview') is not null
drop view myview
go

Create view myview 
with schemabinding 
as 
select a.id, a.name1, b.name2
from dbo.mytable1 a 
join dbo.mytable2 b on a.Id = b.Id
GO

select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
GO

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]
(
    [name1] ASC,
    [name2] ASC
)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]
GO

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.

Answer 2

If you can't change your application code to new objectnames, maybe you can change your application user to use a new default schema and create the indexed views in another schema usng same object names? For instance:

create view iv.MyTest 
as 
 select Col1, Col2 from dbo.MyTest 

Of course this will work only if you have not used the schema names in the application code.

If you have, you could try to move all objects to a new schema and introduce the views in the old schema instead.