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.
-Groups-
Group ID GroupName
-Users-
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 0.1.2.4. 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:
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).