0

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.

Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

1 Answers1

2

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)

enter image description here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Side note @MartinSmith - I have the same situation when I force the hint (instead of it using the cluster index chosen automatically) ... it was less performant AND wanting to do that Key Lookup :( – Pure.Krome Mar 19 '15 at 01:46