Can we use NOLOCK in an Indexed View

This Question is asked by interview, can we use NOLOCK in an indexed view, the answer is no. NOLOCK (READ UNCOMMITTED) is forbidden in indexed views because indexed views are physically stored and transactionally maintained, and NOLOCK allows dirty, phantom, and rolled-back data to be persisted causing permanent corruption.

NOLOCK is disallowed in indexed views because indexed views are incrementally and transactionally maintained physical structures. Allowing dirty or rolled-back rows to be applied would permanently corrupt the view. SQL Server blocks it to preserve correctness and durability.

Except indexed view we can use nolock in other kinds of view. Normal views as don’t store data fetch data from base table every time. Dirty reads vanish after query ends. But Indexed views Store results on disk due to that it must be transactionally correct forever.

Let’s see the demo.

Creating table and inserting few records in this table.

CREATE TABLE dbo.Sales_nolock_demo (     SaleID    INT IDENTITY PRIMARY KEY,     ProductID INT not null,     Amount    MONEY not null ); GO insert into dbo.Sales_nolock_demo(ProductID,Amount) values (10, 1000),(20, 1000),(10, 1000),(10, 1000),(30, 1000)

Creating normal view using nolock.

create view dbo.vw_Sales_nolock_demo as select ProductID,sum(Amount) as Total_Amount from dbo.Sales_nolock_demo (nolock) group by ProductID

View created successfully.

Now see the data from view.

So we can use nolock in the normal view.

See nolock in the indexed view.

Let’s creating a indexed view using the above table.

CREATE VIEW dbo.vw_Sales_Without_nolock_demo WITH SCHEMABINDING AS SELECT ProductID, count_big(*) as cnt, SUM(Amount) AS Total_Amount FROM dbo.Sales_nolock_demo WITH (NOLOCK) GROUP BY ProductID; GO

View created successfully.

Let’s create index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vw_Sales_Without_nolock_demo ON dbo.vw_Sales_Without_nolock_demo(ProductID);

Getting error. Saying that view contains a table hint.