How can I provide an Index Hint to a MS-SQL Server Indexed View?

Question

I have an indexed view FooView.

I've created the following indexes against it:

• CREATE UNIQUE CLUSTERED INDEX IX_Foo1 ON [FooView](SomeId, AnotherId)
• CREATE NONCLUSTERED INDEX IX_Foo2 ON [FooView](SomeId)

Is it possible to use a HINT against IX_Foo2 ? It keeps using IX_Foo1 when I use the WITH (NOEXPAND) hint.

Answer 1

Yes this is perfectly straightforward

CREATE TABLE dbo.FooTable
  (
     SomeId    INT,
     AnotherId INT,
     Filler    CHAR(8000)
  );

go

CREATE VIEW dbo.FooView
WITH SCHEMABINDING
AS
  SELECT SomeId,
         AnotherId,
         Filler
  FROM   dbo.FooTable

GO

CREATE UNIQUE CLUSTERED INDEX IX_Foo1
  ON dbo.FooView(SomeId, AnotherId)

CREATE NONCLUSTERED INDEX IX_Foo2
  ON dbo.FooView(SomeId)

GO

SELECT SomeId
FROM   dbo.FooView WITH (noexpand) --No hint non clustered index chosen automatically

SELECT *
FROM   dbo.FooView WITH (noexpand) --No hint clustered index chosen automatically

SELECT *
FROM   dbo.FooView WITH (noexpand, INDEX = IX_Foo2) --With hint nonclustered index forced

Execution plans

(Note that forcing the index with a hint led to a more expensive plan than leaving the choice up to the optimiser though)