November 20, 2012 at 3:02 pm
I have made a view in SQL that retrieves the distinct values of ID numbers and Names from an archive table. I wanted to put an index on this view, but I'm thinking this just isn't possible. I checked out this blog: http://beyondrelational.com/modules/2/blogs/70/posts/18189/different-ways-to-find-distinct-values.aspx and re-wrote the view, but all the ways to create distinct values listed do not allow indexes on them if in a view. Unless I'm missing something. The view runs fine without an index, but I'd like to know for sure it's not possible before moving on. Thank you for any help.
November 20, 2012 at 5:05 pm
You can create an index, however, there are a lot of conditions that need to be satisfied before you can do that. Some of them are:
1. Underlying table and view must both be created with ANSI_NULLS and ANSI_PADDING on.
2. View has to be created with schemabinding.
3. You cannot use the keyword "distinct" in the view definition. You can use a group by clause with a Count_big operator(Wont accept a count operator).
4. The first index on the view has to be a clustered, unique index so it can materialize the view.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply