Why must the first index on a schema-bound view be UNIQUE CLUSTERED index

SQL Server needs a guaranteed one-to-one row identity and a physical storage structure to deterministically and incrementally maintain persisted view data during base table DML.

A UNIQUE CLUSTERED index provides both row uniqueness and physical ordering, which are mandatory for transactional correctness. The unique clustered index is what causes the view’s data to be stored on disk, similar to how a table data stored. This allows the query optimizer to use the precomputed result set, significantly improving performance for complex queries involving joins and aggregations.

Let’s see the demo

Creating a view  in AdventureWorksDW2019 DB.

USE AdventureWorksDW2019; GO  CREATE OR ALTER VIEW dbo.vw_CustomerSales WITH SCHEMABINDING AS SELECT     CustomerKey,     SUM(SalesAmount) AS TotalSales,     COUNT_BIG(*)     AS OrderCount FROM dbo.FactInternetSales GROUP BY CustomerKey; GO

View created successfully.

Now we are trying to create non cluster index first on this view.

CREATE NONCLUSTERED INDEX IX_vw_CustomerSales_NC ON dbo.vw_CustomerSales (CustomerKey);

Throwing an error

We can not create non cluster index first on the view.

Let’s create cluster index on this view.

CREATE CLUSTERED INDEX IX_vw_CustomerSales_C ON dbo.vw_CustomerSales (CustomerKey);

Again, it will throw an error. Sying that cluster index is not an unique.

CREATE UNIQUE CLUSTERED INDEX IX_vw_CustomerSales_UC ON dbo.vw_CustomerSales (CustomerKey);

Index created successfully.

Now we can create non cluster index on this view.

See the view data.