Sql-server – ndexed Views not permit non-unique clustered indexes

clustered-indexmaterialized-viewsql server

I've been looking into using Indexed Views to increase performance on a few of our most commonly used views.

However Indexed Views do not support non-unique clustered indexes which goes a little against the precedence set by the rest of the database structure.

For example, here is a simplified version of a couple of our tables.

Group ID    GroupName

UserKey    UserName    FullName     GroupID

The indexes are on Groups.GroupID (Non-clustered) and Users.GroupID (Clustered). The clustered key being on GroupID in the Users table as most commonly a range of users from a specific group would be retrieved.
Obviously you would have multiple users per group, so this clustered index is non-unique.

This leaves me a bit uncertain of how to follow this precedence when indexing my views such as this example, as I cannot have a non-unique clustered index.

ConsumableID    ConsumableVariantID AllowThresholdOverwrite FullPath    GroupID ManufacturerID  Type    ModelID
101              29                 1                 4       3               3       2

In actuality the only value on this View which would always be unique is the ConsumableID column, so I am left with little choice as of where to place my index.

Why do Views not permit non-unique clustered indexes when regular tables do?

Best Answer

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).