Why do Indexed Views not permit non-unique clustered indexes?
The following explanation is given in this Microsoft Technical Article:
Why does the first index on a view have to be CLUSTERED and UNIQUE?
It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.
SQL Server uses a system of delta algebra to keep indexed views in step with the base data. It also automatically incorporates view-maintenance query plan operators for each DML query that affects one or more indexed views. Having a unique clustered index on the view greatly simplifies the implementation details.
The current arrangement allows for fixed-shape maintenance operator tree shapes to be incorporated in the base DML query tree, providing orthogonality that also simplifies testing. Ultimately, indexed views could be enhanced one day to support non-unique clustered indexes, but then again all things are possible given unlimited time and boundless resources (neither of which apply to the SQL Server development team as of the time of writing).
For an example showing how complex update query plan building can get, and how easily subtle bugs can creep in, see this example of a bug that occurs with MERGE
and filtered indexes (a feature that has a close connection to indexed views).
In SQL Server all index keys must internally be unique. This is required to obtain lock keys that address exactly one row. It is also required for index maintenance. Imagine an NCI on a column that only has one value in it (100% duplicates). If a row is deleted from the table the storage engine must find the corresponding NCI row and delete it as well. If all NCI rows are indistinguishable this would be impossible.
So you see that the CI on a view must be (internally) unique for the engine to work.
If you don't make an index unique SQL Server still makes it unique internally. In case of an NCI on a heap table it appends the row bookmark. In case of a non-unique CI it adds a uniquifier column. In case of an NCI on a table with a CI it appends any CI key columns that you yourself did not already specify (this might include the uniquifier).
There's no obvious column that could be appended in case of an indexed view. So SQL Server can't automatically do this.
Normally, it is quite obvious for a human what columns you can add to make the view have a unique set of columns to use in the CI. These are normally the PK or CI columns of one of the underlying tables. If the view has a GROUP BY
you normally index on the grouping keys.