Sql-server – Query FROM Indexed View queries underlying table in Execution plan

One idea might be to create a second view that doesn't persist 2.5 million copies of the same value.

CREATE VIEW dbo.innerview
WITH SCHEMABINDING
AS
  SELECT b.Id, SmallId = s.Id
   FROM dbo.Big AS b
   INNER JOIN dbo.Small AS s
   ON b.SmallId = s.Id;
GO
CREATE UNIQUE CLUSTERED INDEX PK_View On innerview([Id]);
GO

Then you can create a view around this, without an index:

ALTER VIEW dbo.View
WITH SCHEMABINDING
AS
  SELECT v.Id, v.SmallId, s.description AS SmallDescription
    FROM dbo.innerview AS v
    INNER JOIN dbo.Small AS s
    ON v.SmallID = s.Id;
GO

Now you can change the small description and it won't affect all those rows, and the queries against dbo.View should exhibit about the same performance as your current queries against the indexed version. Your indexed view shouldn't contain many, many copies of a value that is likely to change.

Of course this view doesn't need to be indexed at all IMHO. A lot of people think indexing a view magically speeds up the query; in a lot of cases it actually just slows down your overall workload. What is the indexed view gaining you that the clustered index on the source table (Big) doesn't already do? Transaction log madness aside, have you compared queries against the indexed view with a simple query like:

SELECT 
  Id, -- please be specific, both tables have a column named Id
  b.SmallId, s.Description AS SmallDescription
FROM dbo.Big AS b
INNER JOIN dbo.Small AS s
    ON b.SmallId = s.Id;

?

See this article: Resolving Indexes on Views for detail on how the query optimizer treats indexed views.

The normal use case for indexed views is aggregations (eg COUNT_BIG, SUM with GROUP BY etc) or complex formulas / calculations you want to keep in one place but do not want to use a computed column. Basically the indexed view would look different to the base table, or to put it another way, you can do things with indexed views you can't do with a base table.

If you created an indexed view that is simply the base table and a unique clustered index (which is compulsory for an indexed view), then conceptually it is a duplicate of the base table and you may have missed the point of indexed views as described above. You have also incurred a considerable index maintenance overhead for no reason.

Indexed View matching, the feature by which the Query Optimizer will use an indexed view even if it is not referenced in the query, (provided that view can 'answer the question') is harder than normal index selection so you also incur a risk of this not happening. Although in your case where the view is a duplicate of the table it is irrelevant. Indexed view matching is also an Enterprise edition feature, although you can override this with the NOEXPAND hint in Standard edition.

None of this is a concern for a conventional non-clustered index which may or may not be used depending on a large number of factors, although selection is probably more straightforward than for indexed views.

In summary, an indexed view implemented as you describe is simply an unnecessary duplicate of the base table. I would be very careful about implementing them, unless there was a specific use case/example where it was shown to be significantly faster using an indexed view, and/or you can live with the overhead having trialled it extensively with your other workloads.

Indexed views do have their uses and there are some interesting discussions on them recently here, here and here.

HTH