Menu
In the SQL Server 2014 Management Studio's object explorer indexed views with schemabinding don't have an index node. Thus, you can create and drop Indexes for views only via T-SQL. The SQL Server Management Studio 2012 still shows the index node.
Has anybody experienced the same or is there some Management Studio setting in version 12.0.2000.8 which I am missing?
Many thanks in advance.
Marcus
779
Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.
You cannot create an index over a view, which is just a query. You can, instead, create an index over a materialized view. A materialized view is a table which is created by evaluating a view, so that you can create an index over it.
It can't contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements. You can't modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
Q: Why can't I use OUTER JOIN in an Indexed view? A: Rows can logically disappear from an Indexed view based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement.
This has been fixed in Cumulative Update 5, check here and download from Microsoft site: https://support.microsoft.com/en-us/kb/3014193
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
