CODEWITHSUNDEEP
sqlsql-servert-sqlindexed-view
Charlieface
Charlieface

Reputation: 71203

Updating an indexed view with NOEXPAND

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);

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?

Update:

It does not help to put the view in a FROM clause, or even in a derived table, a CTE or another view. 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 (recently updated to add NOEXPAND but with no explanation)

Upvotes: 1

Views: 2315

Answers (1)

Cristi Boboc
Cristi Boboc

Reputation: 1

What you want is a nonsense. The update happens always in the table and, based on the view definition, are subsequently applied to the view.

This is the precise order of the execution.

You are saying you want to update the view first and the table after. Even if you think you have said it otherwise, this is what you have said in reality.

Consequently, no matter how you hope to "identify" the rows, the UPDATE statement will go first into the table, identify the rows in the table and change the required values, then it will go to do the same update on the view itself, based on the actual definition of the view.

Please keep in mind: table first, then the view(s) after, not conversely. This is true not only for SQL Server, but for all the other relational database engines that offer a similar functionality as indexed views.

Upvotes: -1

Related Questions