Updating an indexed view with NOEXPAND

Question

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 in the way I would like?

It does not help to put the view in a FROM clause or even in a derived table. As soon as it looks to the parser that it's being used for an update, it fails.

There is no indication that NOEXPAND should not work either in the Table Hints, Indexed View, or Updatable Views documentation. The docs for the UPDATE statement specifically mention that certain table hints are not allowed, but only NOLOCK and READUNCOMMITTED are excluded.

---

So, maybe this is even a bug in SQL Server, and I should file it on Azure Feedback?

Answer 1

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 <X64> (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.