Hint 'noexpand' on object 'ABC_VIEW' is invalid

Question

Hi Team,

Today We restored 3 view from PROD DB (SQL Server 2016 ENT) to QA DB (SQL Server 2016 DEV)

After that we are getting below error from APP side.

Hint 'noexpand' on object 'ABC_VIEW' is invalid

I verified that and refreshed missing index on 3 views. But still getting error.

This is SQL Server 2016 Enteprise Edition

Please help us.

Answer 1

Look at this:

CREATE TABLE alfa (a int NOT NULL PRIMARY KEY)
CREATE TABLE beta (a int NOT NULL PRIMARY KEY)
go
CREATE VIEW gamma WITH SCHEMABINDING AS 
   SELECT a.a
   FROM   dbo.alfa a
   JOIN   dbo.beta b ON a.a = b.a
go
SELECT a FROM dbo.gamma WITH (NOEXPAND)

The SELECT produces the error:

Msg 8171, Level 16, State 2, Line 79 Hint 'noexpand' on object 'dbo.gamma' is invalid.

The NOEXPAND hint is only permitted on indexed views and this is not an indexed view.

Now run:

CREATE UNIQUE CLUSTERED INDEX gamma_ix ON gamma(a)
go
SELECT a FROM dbo.gamma WITH (NOEXPAND)
go
DROP VIEW gamma
DROP TABLE alfa, beta

There is no error message at this time.

I verified that and refreshed missing index on 3 views. But still getting error.

I guess you need to re-verify and add the indexes for real this time.

Answer 2

Hi @Vijay Kumar

Maybe this link can help you.

https://social.technet.microsoft.com/Forums/sqlserver/en-US/ce7da132-3db1-4c49-80d2-060a243f6e96/how-to-solve-hint-noexpand-on-object-lttablegtquot

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.