Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
There's a very brief explanation near the bottom of this page entitled 'Why does the first index on a view have to be CLUSTERED and UNIQUE?' but it doesn't do much detail. I'd definitely be interested to hear a more detailed explanation. –  Steve Pettifer Apr 14 at 10:00
3  
A couple of comments: 1 - There's no reason you can't cluster on (GroupID, UserID). Don't limit yourself to a single column for the key. 2 - I imagine the limitation for a view is because this is a supplemental data object that needs to have rows easily tied to the NC indexes. For a table, the non-unique CI key gets an int appended to it, but I think that would be more challenging with an indexed view since it's not an actual table but needs to REFLECT an actual table. –  JNK Apr 14 at 12:32

2 Answers 2

up vote 12 down vote accepted

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 (PDF technical paper).

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

share|improve this answer
    
A similar bug may occur if you try to update an indexed view which has a GROUP BY clause but not all grouping expressions are keys in the clustered index. It's valid as of SQL Server 2014. –  Quassnoi Oct 8 at 18:50

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.

share|improve this answer
1  
I strongly suggest revising the phrasing of this answer. While it contains a valid point regarding the original question, it may look like it suggests that all non unique indexes contain uniquifiers, which is not the case. –  spaghettidba Apr 14 at 12:57
    
@spaghettidba thanks, I did not notice that. Hope it's better now. –  usr Apr 14 at 13:33
    
Sorry, not yet. You're mixing two things together. Nonclustered indexes do not have to be unique and are not uniquified internally: you're not making this point clear enough. Everything you say in your answer applies to clustered indexes only. –  spaghettidba Apr 14 at 14:11
    
@spaghettidba NCIs are always unique internally. They can always output all CI keys as part of a query plan. See pastebin.com/vkGHpCsR The NCI data page contains both columns. –  usr Apr 14 at 14:17
    
I see where you're coming from. Multiple leafs can share the same index key, but the clustering key is always included in NCIs. Is it enough to say that they're always unique internally? I don't think so. –  spaghettidba Apr 14 at 15:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.