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?