All we need is an easy explanation of the problem, so here it is.
In SSMS 2008R2 you can navigate to a Database > Views > Find a specific view that you have an index on and unfurl the Index(es)
In SSMS 2014 (possibly other modern versions) you cannot. When you get to the View and unfurl, the selection of object types below it does not include Indexes – thus you cannot see the Indexes via the GUI. Of course I can still use TSQL to view the index, but this is not as easy to demonstrate to colleagues as via the GUI or for them to do themselves.
Does anyone know why this happens or if there is a way to allow the Indexes on Views to be displayed (I believe you cannot see the Indexes on any object within System Tables either, but that may be related to the same issue)? I’m guessing there is a setting in SSMS, but I haven’t been able to find/answer via the net so far.
Steps to reproduce:
- Create a view (don’t forget "WITH SCHEMABINDING AS" option)
- Create an Index against that view
- Navigate to your view within SSMS 2014
- When you unfurl that view (i.e. click the cross), there is not a "folder" for Indexes (I’ve just noticed, there isn’t one for Columns either)
If you have access to SSMS from 2008R2 you will find that you can navigate to the Indexes on a View and also display the columns list.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
As J.D. suggested in the comments, try with the latest version of SSMS. I evaluated with 18.8 using the below:
USE tempdb GO CREATE VIEW dbo._IndexView WITH SCHEMABINDING AS SELECT 1 AS One GO CREATE UNIQUE CLUSTERED INDEX One ON dbo._IndexView (One) --DROP VIEW dbo._IndexView
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂