6

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?

I believe there is an optimization called rowset-sharing which may make a difference here, does anyone know about this?


Update:

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. What do people think?

1
  • Interesting question for sure, unfortunately I don't have much input for you at the moment but will be researching this and will keep you posted. I will say index views do have their quirks about them as blogged by Erik Darling here but that's kind of the other side of the coin type of problems, so not really related to what you're experiencing. Could you please add the execution plans of your different UPDATE queries? (You can upload the plans to PasteThePlan.) – J.D. Dec 28 '20 at 22:51
7

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

enter image description here

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.

3
  • I'll have to see if I can repro on a Developer Edition. – Charlieface Dec 28 '20 at 23:53
  • Sorry downvoted meant to upvote. That query plan is how I would have expected. Doesn't really answer the problem though, because view matching doesn't always work, and obviously Standard Edition is an issue. I suppose typical of MS they will close it as By Design because of the new docs. – Charlieface Dec 28 '20 at 23:59
  • 2
    That's why it should be an enhancement request. There's little chance it will be accepted as a bug. – David Browne - Microsoft Dec 29 '20 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.