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
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?
I believe there is an optimization called rowset-sharing which may make a difference here, does anyone know about this?
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
READUNCOMMITTED are excluded.
So, maybe this is even a bug in SQL Server, and I should file it on Azure Feedback. What do people think?