Updating an indexed view with NOEXPAND

I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.

I am not doing this simply to get more views on the question, I would like the DBA community to share their view on whether this might even be a bug in SQL Server. I don't think the SO community has the expertise to decide that, so I'm reposting here.

Suppose I have a table T, and I have an indexed view V on it:

CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);

In this trivial example it's basically just a filtered index, but it could also have joins and such like.

I would now like to select some rows in T where b = 1, the filtered view here is very useful, and I'm on Standard so have to use NOEXPAND (or it's too complex for view matching):

SELECT Id, txt
FROM V WITH (NOEXPAND);

This works nicely.

Now I want to update these rows to some value. The view qualifies as updatable so I can do this:

UPDATE V
SET txt = 'Foo';

This does not use the indexed view to find the rows to update, even though it needs them to actually update the view. What I would like it to do is use the view like a normal table index, and identify the rows to update from it, pass them to the Clustered Index Update on T, followed by an Update on the view. So I try this:

UPDATE V WITH (NOEXPAND)
SET txt = 'Foo';

This fails with "Hint 'noexpand' on object 'V' is invalid.".

I know I can get round it with a query such as this:

UPDATE T
SET txt = 'Foo'
FROM T
JOIN V WITH (NOEXPAND) ON V.Id = T.Id;

But this means an extra Seek. Not only that, it adds a Filter on the subsequent indexed view update to check the rows match the view (a joined view would require the joins to be evaluated) when clearly they must match the view.

Is there any way of getting this to work i

This works as you want on Enterprise/Developer Edition:

use tempdb
go

CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;
 

GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);
GO

UPDATE V
SET txt = 'Foo';

The update has this plan

running

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) 
    Sep 23 2020 16:03:08 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 19042: ) (Hypervisor)

You can add a feedback item here to request that NOEXPAND be supported on UPDATE queries. I put in a PR to update the docs to clarify that NOEXPAND is not available on UPDATE.

StackExchange Database Administrators Q#282265, answer score: 7