Timeline for Unable to create a Filtered Index on a Computed Column
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot | replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/ | |
| Aug 23, 2016 at 12:57 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added 618 characters in body |
| Aug 23, 2016 at 12:37 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added CONVERT to the view definition, |
| Aug 23, 2016 at 12:33 | comment | added | Hannah Vernon♦ | Note, the non-clustered index is not a covering index, and as such any query that either filters on, joins, or returns columns from either the view or the underlying table will need to perform a key-lookup operation against the base table or the view. It is likely that for a real-world scenario, the limited scope of my answer could be expounded with even better performance in mind. | |
| Aug 23, 2016 at 12:29 | comment | added | Hannah Vernon♦ | Interesting question, @BobBryan - the clustered index is required to enable the view to be persisted, although it doesn't actually need to be a unique index. I could have created the view's clustered index on some other column, such as the TestComputedColumn instead. However, since the clustered index contains all the data for the table/view, I decided it would be likely be better to use a monotonically increasing number as the clustering key. Note, I didn't actually test that supposition, and it may in fact be incorrect for some variations of the repro. | |
| Aug 23, 2016 at 2:37 | comment | added | Bob Bryan | The work around shows that both a clustered index and a nonclustered index is created on the view. Does the nonclustered index have to be used over the clustered index for some reason? Or, is the nonclustered index more performant? If the clustered index were used in the query, what would the statistics show? | |
| Sep 29, 2015 at 17:10 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added 156 characters in body |
| Sep 29, 2015 at 16:24 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added 768 characters in body |
| Sep 28, 2015 at 16:49 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added 315 characters in body |
| Sep 28, 2015 at 16:43 | history | edited | Hannah Vernon♦ | CC BY-SA 3.0 | added 315 characters in body |
| Sep 28, 2015 at 16:19 | history | answered | Hannah Vernon♦ | CC BY-SA 3.0 |