Why is GETDATE() ,NEWID(),RAND() banned but SUM() allowed in indexed view

This is an interview question and asked by the interviewer that sum() is allowed but other aggregate function or other function like gatedate(), newID() , RAND() etc are not allowed in the indexed view why?

Let’s see why this allowed

Indexed views physically store results and rely on deterministic, incrementally maintainable expressions. Non-deterministic functions like GETDATE(), NEWID(), and RAND() would corrupt storage and break optimizer guarantees, while SUM() is mathematically stable and safe. 

An indexed view is not a virtual object. When we are creating a clustered index on the view then it persists rows and maintained synchronously on every DML. A table maintained automatically by SQL Server. Therefore, results must never change unless base data changes.

Deterministic means Same input then always same output. Non-Deterministic means Same input then different output.

Read more : Deterministic and Nondeterministic function in SQL Server

 https://bageshkumarbagi-msbi.blogspot.com/2024/12/deterministic-and-nondeterministic.html

Let’s see the demo

For the demo creating a table and inserting few records

CREATE TABLE dbo.Salesdemo (     SaleID INT IDENTITY PRIMARY KEY,     CustomerID INT NOT NULL,     Amount MONEY NOT NULL );  INSERT INTO dbo.Salesdemo (CustomerID, Amount) VALUES (1, 100), (1, 200), (2, 300), (2, 400);

Table created successfully. now creating view

CREATE VIEW dbo.vw_Sales_getdate_demo with schemabinding AS SELECT     CustomerID,               Amount,               getdate() as runtime FROM dbo.Salesdemo

View created successfully. Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesAgg ON dbo.vw_Sales_getdate_demo (CustomerID);

Not able to create index on this view.

Let’s created index view on the view which is using sum.

CREATE VIEW dbo.vw_Sales_demo with schemabinding AS SELECT     CustomerID,     sum(Amount) as totalAmount         FROM dbo.Salesdemo group by CustomerID

View created. Now creating index on this view.

Oooo! Not able to create index on this view. In the view we need to add count_big(*) then only we can able to create index on this view.

Let’s see

CREATE VIEW dbo.vw_Sales_demo with schemabinding AS SELECT     CustomerID,               count_Big(*) as cnt,               sum(Amount) as totalAmount              FROM dbo.Salesdemo group by CustomerID

Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesAgg ON dbo.vw_Sales_demo (CustomerID);

Index created successfully.

Let’s run this view.

Keep in mind when we are using sum the column in the base table must be not null. If that is null then we are not able to create index view.

Let’s see this demo.

Creating a table with null column.

CREATE TABLE dbo.SalesNullDemo (     SaleID INT IDENTITY PRIMARY KEY,     CustomerID INT NOT NULL,     Amount MONEY  NULL );  INSERT INTO dbo.SalesNullDemo (CustomerID, Amount) VALUES (1, 100), (1, 200), (2, 300), (2, 400);

Now creating view

CREATE VIEW dbo.vw_Sales_Null_demo with schemabinding AS SELECT     CustomerID,               count_Big(*) as cnt,               sum(Amount) as totalAmount              FROM dbo.SalesNullDemo group by CustomerID

View created successfully. Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesAgg ON dbo.vw_Sales_Null_demo (CustomerID);

Not able to create index on this view. Getting the above error.