Why do Indexed Views not permit non-unique clustered indexes?

Question

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?

Answer 1

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

Answer 2

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.