Computed column with indexed view

Computed columns can be used in indexed views only when they are deterministic, precise, persisted, and schema-bound, because indexed views are physically stored and incrementally maintained. SQL Server must be able to update their values inside the storage engine without relying on session semantics or runtime expression evaluation.

In one word we can say that if computed column is persisted then we can use it in the Indexed view and if column is not persisted then we can’t use it in the indexed view.

Learn more about computed columns: 

https://bageshkumarbagi-msbi.blogspot.com/2017/07/computed-column-in-sql-server.html

Let’s see the demo.

Creating a table with computed column.

Create table tblOrder_non_persisted ( OrderID bigint not null, Qty int not null, Price decimal(18,2) not null, Total_Price as Qty * price ) INSERT INTO tblOrder_non_persisted(OrderID,Qty,Price) Values (1,10,12.5), (2,5,130.35), (3,100,50.5), (4,54,80)

Table created with computed column. Computed column is not persisted column. Let’s create a indexed view on this table.

CREATE VIEW vw_tblOrder_non_persisted WITH SCHEMABINDING AS SELECT    OrderID,    SUM(Total_Price) AS TotalSales,    COUNT_BIG(*) AS Cnt FROM dbo.tblOrder_non_persisted GROUP BY OrderID; GO

View is created successfully. Let’s create index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_tblOrder_non_persisted ON Vw_tblOrder_non_persisted(OrderID);

Ooo! Getting the below error

So indexed view is not created on the computed column which is not persisted.

See the other example.

Creating a table inserting records and creating view.

Create table tblOrder_persisted ( OrderID bigint not null, Qty int not null, Price decimal(18,2) not null, Total_Price as Qty * price persisted ) INSERT INTO tblOrder_persisted(OrderID,Qty,Price) Values (1,10,12.5), (2,5,130.35), (3,100,50.5), (4,54,80) go  CREATE VIEW vw_tblOrder_persisted WITH SCHEMABINDING AS SELECT    OrderID,    SUM(Total_Price) AS TotalSales,    COUNT_BIG(*) AS Cnt FROM dbo.tblOrder_persisted GROUP BY OrderID; GO

Now creating index on this view

CREATE UNIQUE CLUSTERED INDEX IX_tblOrder_persisted ON Vw_tblOrder_persisted(OrderID);

Indexed view created successfully.

Indexed view created successfully.